Replace Method Generates Application-Defined or Object-Defined Error in Excel for Mac
I'm Having Trouble with This on the Latest Version of Excel for Mac. Here's the Macro: Sub Run() ' ' Run Macro ' ' Sheets("Data"). Select Cells. Select...
I'm having trouble with this on the latest version of Excel for Mac.
Here's the macro:
Sub Run()
'
' Run Macro
'
'
Sheets("Data").Select
Cells.Select
Range("BK1").Activate
Selection.Replace What:="unknown", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Pivot Table").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Sheets("Formatted Data").Select
ActiveWorkbook.Worksheets("Formatted Data").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Formatted Data").AutoFilter.Sort.SortFields.Add Key _
:=Range("A4"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Formatted Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
When I run it, I get a MSFT Visual Basic error:
Run-time error '1004':
Application-defined or object-defined error
If I remove this part, it runs fine, but isn't exactly what I need in terms of final result:
Sheets("Data").Select
Cells.Select
Range("BK1").Activate
Selection.Replace What:="unknown", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
UPDATE
If I remove just this little part, the macro runs, but not sure if I lose anything by removing this:
SearchFormat:=False, _
ReplaceFormat:=False
1 Answer
Ahh, found some reference (in a comment on SO, and in the MS forums) that the SearchFormat option isn't valid in the Mac version of Excel.
Others may not be as well, but I can't find any documentation on the OSX version of Office VBA, so I can't really confirm it directly from MS.