I am developing a VBA module. I am pasting the contents of a dictionary into a sheet and some values get auto formatted.
I have looked up many articles on how to keep Excel from auto formatting data.
I have tried formatting the cells via "Format Cells" to General, Text, and (attempted) custom formats (e.g. #" A") before pasting.
Also, I have tried formatting the cells via Text-to-Columns before pasting as well. I tried General and Text here.
Finally, I have tried using Selection.NumberFormat = "General" and Selection.NumberFormat = "Text" as well as Selection.TextToColumns Destination:=Range("B:B"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _ :="=", FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
I have even tried doing Find & Replaces after the auto formatting takes place. For example, after pasting and the auto formatting I would do a Find & Replace to change "1:00:00 AM" to "1 Amps" AND THEN "Amps" to "A". This was to see if I could just replace a partial string in the selection with what I wanted. That did not work either.
The impacted values are for amperage ratings. For example, I am performing Find & Replaces to change "1 Amp" to "1 A".
No matter what I do it keeps auto formatting to a time like "1:00:00 AM".
Simply set the number format of the cell to text, and that will resolve your issue.
Have you tried setting the cell's number format to text before populating the cell? The vba would be something like
Range("A1").NumberFormat = "@"