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.
