Run-time error 9 for VBA on Mac


Board Regular
May 23, 2013
Hi Excel Gurus,

Back with yet another Mac vs. Windows VBA question.

Created code on Windows 10 machine. My code breaks a little later with Mac Excel version 15.24 than it did for version 15.29 (help requested for error 1004 in another post). This time I get :

a Run-time error '9':

Subscript out of range

... when trying to do a find and replace with Variants. The code finds a column "Recording Prompts_Edit" and copies its content to another column "Application TTS". The code uses a Match function to find these columns. Once the content is copied, the code scrubs through the newly pasted cells and replaces any words with apostrophes with full words. The find and replace words are found inside a dynamic array and each find word has a replacement word so they arrays match up numerically.

Below is the code and the error happens inside the For x ... Next x loop. It is not a problem on the Windows machine.

Sub CopyTTStoApp()

Application.ScreenUpdating = False

    Dim AudioFilePathText As String
    Dim AudioFilePathText2 As String
    Dim ColCount As Integer
    Dim ColStep As Integer
    Dim ColStep2 As Integer
    Dim MyCol As Variant
    Dim MyCol2 As Variant
    Dim n As Long
    Dim mySheetCount As Long
    mySheetCount = ActiveWorkbook.Sheets.Count

    Dim i As Long
    For i = 5 To mySheetCount Step 1

        ColCount = ActiveSheet.UsedRange.Columns.Count

                    MyCol = Application.Match("Recording Prompts_Edit", Range(Cells(1, 1), Cells(1, ColCount)), 0)
                    n = Cells(Rows.Count, MyCol).End(xlUp).Row

                    MyCol2 = Application.Match("Application TTS", Range(Cells(1, 1), Cells(1, ColCount)), 0)

  'Copies Recording Prompts to Application TTS column
        Range(Cells(2, MyCol), Cells(n, MyCol)).Select
        'Selects the range in the Application TTS column to paste
        Range(Cells(2, MyCol2), Cells(n, MyCol2)).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
 'Remove the apostrophe...
                    Dim fndList As Variant
                    Dim rplcList As Variant
                    Dim x As Long
                    Dim r As Long

                    Dim rng As Range
                    Dim cell As Range
                    Dim rngCell As Range
                    'Set rng = Range(Cells(2, MyCol2), Cells(n, MyCol2))

                    fndList = Array("I’ve", "you’ll", "won't", "I'll", "you’re", "you've", "We've", "weren't", "can't", "eBay's", "I've", "you'll", "you're")
                    rplcList = Array("I have", "you will", "will not", "I will", "you are", " you have", "we have", "were not", "cannot", "eBays", "I have", "you will", "you are")

                        For x = LBound(fndList) To UBound(fndList)

'[HERE is where the error gets thrown and the highlight in yellow in debug more starts @  .Range(Cells(2........]

                            With Sheets(i)
                                .Range(Cells(2, MyCol2), Cells(n, MyCol2)).Replace What:=fndList(x), Replacement:=rplcList(x), LookAt:=xlPart, _
                            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                            End With

                        Next x
  Call anotherModule
  Call anotherModule2

  ' next sheet
  Next i

        Application.ScreenUpdating = True
End Sub

I never thought I'd have success creating VBA that can run on Macs but it turns out some of my complicated macros do indeed work! So I'm continuing down this path hoping that I will find success with my most recent project. Naturally, I can't be successful without the wonderful help I receive in this forum.


Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back