Why can't I set Application.ReplaceFormat.NumberFormat to some valid formats?

Question

I'm trying to use an Excel Macro to reformat a spreadsheet exported using OLE-Automation

The following code works OK:

Application.FindFormat.NumberFormat = "#,##0.0000000"
Application.ReplaceFormat.NumberFormat = "#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

if I change the ReplaceFormat to

Application.ReplaceFormat.NumberFormat = "#,##0.0" 

to display only 1 decimal place, I get an error 1004 (Application-defined or object-defined error). "0.0" fails as well.

I can set the cell format (Cells.NumberFormat) to "#,##0.0"

I have only tried this against Excel-2003 as it is the only version I have available.

Answer 1

I have found part of the answer. For this to work, the NumberFormat must already exist in the workbook.

A work-around is to set the format of a cell to "#,##0.0", then do the replace:

Worksheets("Sheet1").Range("A1").NumberFormat = "#,##0.0" 
Application.FindFormat.NumberFormat = "#,##0.0000000"
Application.ReplaceFormat.NumberFormat = "#,##0.00"
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

There doesn't seem to be any Collections that allow me to get at the custom number formats (According to this site anyway).

I discovered this when setting Application.FindFormat to a new format started throwing errors!