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
    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
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Elena Rostova

Elena Rostova

Lead Health, Wellness & Medical Journalist

Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.

Share this article
Twitter Facebook Pinterest