Run-time error 9 for VBA on Mac

[Matt_O]

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
    
        Sheets(i).Select
    
        Range("A1").Select
    


        
        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
        
        Selection.Copy
        
        '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
        
                    Range("A1").Select
                    
                    '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, _
                            ReplaceFormat:=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.

Cheers,
Matt