title: VBA - Subscript out of range error
body: <p>I am trying to write a macro to do the following:</p>
  
  <ol>
  <li>Prompt user to open their file, then add new "Mismatch" sheet to the
  file</li>
  <li>Find the column name of "Cust Bill To ID" &amp; "SAP CMF#", and store
  the data below these two columns to 2 different arrays[BTID() &amp;
  CMF()].</li>
  <li>If BTID(i) is not equal to CMF(i), copy entire row and paste it to
  Mismatch sheet.</li>
  </ol>
  
  <p>But the arrays having the subscription out of range error and the Mismatch sheet only have the column name duplicate from the original sheet (the data are missing).</p>
  
  <p>Result:<br>
   <a href="https://i.stack.imgur.com/YaCd4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YaCd4.png" alt="enter image description here"></a></p>
  
  <p>Code:</p>
  
  <pre><code>Sub Mismatch()
  
  Dim sht As Worksheet
  Dim authSht As Worksheet ' Renamed this variable
  Dim misSht As Worksheet ' Added a worksheet variable
  Dim i As Integer
  Dim k As Integer
  Dim last As Integer
  Dim BTID() As String
  Dim CMF() As String
  Dim rng1 As Range ' Added this variable
  Dim rng2 As Range ' Added this variable
  
  
  ''OPEN FILE
  sFileName = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx;*.xlsm;*.xla;*.xlam),*.xls;*.xlsx;*.xlsm;*.xla;*.xlam, All Files (*.*), *.*", 1, "Select Authorization Issued Report File")
  If sFileName = "False" Then Exit Sub
  
  Application.DisplayAlerts = False
  Set auth = Workbooks.Open(sFileName, UpdateLinks:=xlUpdateLinksNever)
  
  'add new sheet
  Set sht = Sheets.Add
  sht.Name = "Mismatch"
  
  Sheets("Mismatch").Select
  With ActiveWorkbook.Sheets("Mismatch").Tab
      .Color = 255
      .TintAndShade = 0
  End With
  
  
  Set authSht = Worksheets("Authorizations Issued")
  Set misSht = Worksheets("Mismatch")
  
  
  ''find Mismatch
  authSht.Range("A2:BT2").Copy Destination:=misSht.Range("A1")
  
      last = ActiveSheet.UsedRange.Rows.Count
      'col = ActiveSheet.End(xlToLeft).Column
      Set rng1 = authSht.Range("A2:BH2")
      Set rng2 = rng1
  
  
      For Each c In rng1.Cells
          If c.Value = "Cust Bill To ID" Then Set rng1 = c
      Next c
      For Each c In rng2.Cells
          If c.Value = "SAP CMF#" Then Set rng2 = c
      Next c
  
      Dim l As Integer
      l = 2
      ReDim BTID(2 To l)
      ReDim CMF(2 To l)
  
      For i = 2 To last
          BTID(i) = rng1.Offset(i, 0).Value
          CMF(i) = rng2.Offset(i, 0).Value
          If i &lt; last Then
              ReDim Preserve BTID(1 To i + 1)
              ReDim Preserve CMF(1 To i + 1)
          End If
      Next
  
      For k = 2 To last
          If BTID(k) = CMF(k) Then
              authSht.Range("A" &amp; k &amp; ":BH" &amp; k).Copy Destination:=misSht.Range("A" &amp; l)
              l = l + 1
  
          Else: l = l
  
          End If
      Next
  
  
  misSht.UsedRange.EntireColumn.AutoFit
  
  
  
  End Sub
  </code></pre>
  
  <p>And I realized that the code below doesn't work in the for loop. </p>
  
  <pre><code> authSht.Range("A" &amp; k &amp; ":BH" &amp; k).Copy Destination:=misSht.Range("A" &amp; l)
  </code></pre>
  
  <p>What's wrong with this code?</p>