• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

PC Excel VBA Macro for Mac Excel 2011

I have a VBA Macro created on a PC in Excel VBA; that does not run on the Mac version of Excel 2011.  The error I get is on the "set rFine = " line...

Anyone know how I can get this macro to run on the Mac version of Excel 2011?

Sub FixDimension()
    Dim rFind As Range, sFind(), sReplace(), sOffset(), i As Long
    sFind = Array("Full Banner - 468 x 60", "Skyscraper - 120 x 600", "Medium Rectangle - 300 x 250", _
                  "Super Banner - 728 x 90", "GEOPOP - 1 x 1", "780x260 - 780 x 260", _
                  "Slider - 1 x 1", "Raw Click - 1 x 1", "Interstitial - 1 x 1", "Pixel/Popup - 1 x 1")
    sReplace = Array("468x60", "120x600", "300x250", "728x90", "GeoPop", "Bottom of Page", _
                     "Messenger Ad", "Raw Click", "Interstitial", "Pop Under")
    sOffset = Array("CPM", "CPM", "CPM", "CPM", "GeoPop", "CPM", "Messenger Ad", "Raw Click", "Interstitial", "Pop Under")
    With ActiveSheet.UsedRange
        For i = LBound(sFind) To UBound(sFind)
            Set rFind = .Find(What:=sFind(i), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
            If Not rFind Is Nothing Then
                    rFind.Value = sReplace(i)
                    rFind.Offset(, -1) = sOffset(i)
                    Set rFind = .FindNext(rFind)
                Loop While Not rFind Is Nothing
            End If
        Next i
    End With

End Sub

Open in new window

1 Solution
What is the Error and what is the value of sFind(i) when the error occurs?

Rory ArchibaldCommented:
Change line 14 to:
Set rFind = .Find(What:=sFind(i), LookAt:=xlWhole, MatchCase:=False)

Open in new window


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now