Application.FindFormat.NumberFormat Help! (1 Viewer)

[stephen81]

I'm trying to replace all the dates in my workbook in a specific date format with a different format using the following code...

With Application
    .FindFormat.NumberFormat = "dd-mmm-yyyy"
    .ReplaceFormat.NumberFormat = "mm/dd/yyyy"
End With

Cells.Replace What:="-", Replacement:="-", LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True

Each time I run it though, even if these are the only lines existing in my macro, I get a generic Run-time error '1004': Application-defined or object-defined error

In debug mode if I hover over the
FindFormat.NumberFormat = "dd-mmm-yyyy"
and
ReplaceFormat.NumberFormat = "dd-mmm-yyyy"
lines, it pops up with

.FindFormat.NumberFormat = null
and
.ReplaceFormat.NumberFormat = null.


Anyone got any ideas?

 

[chergh]

You want to refer to the worksheet object and not the application object.

Instead of

with application

use

with application.workbooks("WorkbookName.xls").worksheets("WorksheetName")