CODEWITHSUNDEEP
excelvba
user1987607
user1987607

Reputation: 2157

excel vba change format of date - numbers

Excel changes the following values automatically to a number, I guess because he considers them as a date:

2/2/1 becomes 36527 4/2/1 becomes 36926

I have a column with a combination of different formats now:

2/1/
3/1/
8/7/
36527
1/0/0
36926

Which VBA code can I use to convert the numbers back to their original format? The other values should stay the same.

I know the cDate function, but I guess it's not useful here?

I have already this in my VBA code for pasting values

ActiveWorkbook.Sheets("Import").Columns("A:AH").NumberFormat = "@"
ActiveWorkbook.Sheets("Import").Range("A1").Select
ActiveSheet.Paste

Upvotes: 0

Views: 231

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

You can't change them back. Once Excel converts them, the original value is gone. Before you input the value, you can prepend an apostrophe to force it to text.

ActiveCell.Value = "'" & sMyValue

or as @Scott Craner commented, you can format the cell as text

ActiveCell.NumberFormat = "@"
ActiveCell.Value = sMyValue

Upvotes: 1

Related Questions