General number formats changing to Dates or Euros

[KMH]

What causes Excel 2007 to change general number formats from general to dates
or euros? How can I easily fix a big model that has lots of this occuring
all over it?

Thanks,
Ken

 

[Gary''s Student]

You must be very careful about entering data. Even if a cell is formatted
general, entering something like 1/1 will cause to "re-format" as a Date.

 

[Paul C]

There is some kind of glitch some where deep in the bowels of Excel. I have
also had this happen and have scanned the discussion groups to find that I am
not the only one with this problem. (mine was also dates and Euros by the way)

some code like this can remove the formats
sub datefixer()
TgtWorkbookName = ActiveWorkbook.Name
For Each Sh In Workbooks(TgtWorkbookName).Worksheets
For Each Cell In Sh.UsedRange.Cells
If Cell.NumberFormat = "[$-409]d-mmm-yy;@" Then
Cell.NumberFormat = "General"
End if
Next Cell
Next Sh

end sub

Note that this fixes a specific format namely "[$-409]d-mmm-yy;@" which was
my bad date one that was showing up for me.
You can change it to correct other ones also. It take a while to run on big
workbooks

to find the exact coding for other bad formats just gather by directly
referencing the cell

badformat=Range("A2").NumberFormat

sub datefixer()
dim badformat as string
TgtWorkbookName = ActiveWorkbook.Name
badformat=Range("A2").NumberFormat
For Each Sh In Workbooks(TgtWorkbookName).Worksheets
For Each Cell In Sh.UsedRange.Cells
If Cell.NumberFormat = badformat Then
Cell.NumberFormat = "General"
End if
Next Cell
Next Sh

end sub

You can get a lot fancier and gather all the used formats and do some fancy
loopin to purge them, but this is simple and does the trick.

One caution, this will change all formats back to general (or whatever other
format you may want to change it to)

If there are dates you want to keep you would either need to code in
exceptions or put them back later

 

[KMH]

Thanks Paul and Gary's Student.

I used a combination of your ways, but found another as well. Below changes
the default "Normal" Style back to General Number Format. So far this seems
to work.

Sub FixDefaultNumFormat()
ActiveWorkbook.Styles("Normal").NumberFormat = "General"
End Sub

Thanks again

There is some kind of glitch some where deep in the bowels of Excel. I have
also had this happen and have scanned the discussion groups to find that I am
not the only one with this problem. (mine was also dates and Euros by the way)

some code like this can remove the formats
sub datefixer()
TgtWorkbookName = ActiveWorkbook.Name
For Each Sh In Workbooks(TgtWorkbookName).Worksheets
For Each Cell In Sh.UsedRange.Cells
If Cell.NumberFormat = "[$-409]d-mmm-yy;@" Then
Cell.NumberFormat = "General"
End if
Next Cell
Next Sh

end sub

Note that this fixes a specific format namely "[$-409]d-mmm-yy;@" which was
my bad date one that was showing up for me.
You can change it to correct other ones also. It take a while to run on big
workbooks

to find the exact coding for other bad formats just gather by directly
referencing the cell

badformat=Range("A2").NumberFormat

sub datefixer()
dim badformat as string
TgtWorkbookName = ActiveWorkbook.Name
badformat=Range("A2").NumberFormat
For Each Sh In Workbooks(TgtWorkbookName).Worksheets
For Each Cell In Sh.UsedRange.Cells
If Cell.NumberFormat = badformat Then
Cell.NumberFormat = "General"
End if
Next Cell
Next Sh

end sub

You can get a lot fancier and gather all the used formats and do some fancy
loopin to purge them, but this is simple and does the trick.

One caution, this will change all formats back to general (or whatever other
format you may want to change it to)

If there are dates you want to keep you would either need to code in
exceptions or put them back later

--
If this helps, please remember to click yes.

What causes Excel 2007 to change general number formats from general to dates
or euros? How can I easily fix a big model that has lots of this occuring
all over it?

Thanks,
Ken