Geek Answers Handbook

Why can't I install Application.ReplaceFormat.NumberFormat in some valid formats?

I am trying to use an Excel macro to reformat a spreadsheet exported using OLE-Automation

The following code works fine:

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 changed ReplaceFormat to

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

to display only 1 decimal place, I get error 1004 (User Defined or Object Error). "0.0" also does not work.

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

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

+3
source share
2 answers

. , NumberFormat .

- "#, ## 0.0", :

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

, - , ( ).

Application.FindFormat , !

+1

'1004': , -

, . , dateformat .

Sub datum_popravak_rucno()
    ' add find format to cell A1
    ActiveSheet.Range("A1").NumberFormat = "m/d/yyyy"
    ' add replace format to cell B1
    ActiveSheet.Range("B1").NumberFormat = "yyyy-mm-dd hh:mm:ss"
    ' define find format
    Application.FindFormat.NumberFormat = "m/d/yyyy"
    ' define replace format
    Application.ReplaceFormat.NumberFormat = "yyyy-mm-dd hh:mm:ss"
    ' do replace on all cells
    Cells.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByColumns, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

Excel 2003, 2007, 2010!

+1

More articles:


All Articles