Excel Auto Formatting

0
votes

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".

1
excelvbaautoformatting
Have you tried setting the cell's number format to text before populating the cell? The vba would be something like Range("A1").NumberFormat = "@" – basodre
That worked! Thank you @basodre! I am not proficient enough in VBA to know it was going to be that easy of a fix. So, I looked it up and the "@" means Text. Why does this work and everything else I tried in regards to formatting as text not work? Also, I assume that this will take all of my dictionary keys and items as is and not apply any auto formatting. Is that correct? – Frosty_Fraz
Text just treats the entry as a block of text, exactly as it is. When you choose a different format, it's still a number and the system tries to determine what type it should be. – basodre
That makes sense, but are you saying that something like Text-to-Columns only chooses a different format and does not change the metadata or (back-end) formatting of the cell? Because I chose Text when using Text-to-Columns as well as with the .NumberFormat property. – Frosty_Fraz
@basodre I cannot mark your comment as an answer. I would like to give you credit for it so if you could post it in a way in which I could mark it as an answer then that would be great! Thank you! – Frosty_Fraz

1 Answers

0
votes

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 = "@"