Prev: Pivot table bottom ten filter with conditions
Next: Pivot Table Format Preservation
From: KMH on 29 Jan 2010 21:35
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

"Paul C" wrote:

> 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.
>
>
> "KMH" wrote:
>
> > 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
 | 
Pages: 1
Prev: Pivot table bottom ten filter with conditions
Next: Pivot Table Format Preservation