title: VBA: relative cell reference in loop
body: <p>I am trying to use relative reference in a loop. My aim is to use index and match combined to find values independent of how many rows and columns are present in the file. This is what I have created so far, but it does not work. The data is the the same stored on two different sheets.</p>
  
  <pre><code> Error msg: Run-time error '1004':
  </code></pre>
  
  <p>Unable to get the Match Property of the <code>WorksheetFunction</code> class</p>
  
  <ol>
  <li><p>Sub testing()</p>
  
  <pre><code>Dim ActSor As Long, ActOsz As Long
  
  ActSor = ActiveSheet.UsedRange.Rows.Count 
  ActOsz = ActiveSheet.UsedRange.Columns.Count
  
  Dim ws1 As Worksheet, ws2 As Worksheet
  
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  
  Dim Rg As Range
  
  Set Rg = ws2.Range("B2", Cells(ActSor, ActOsz))
  
  Dim RgActOsz As Long
  
  RgActOsz = Rg.Columns.Count
  
  Dim i As Long , sor As Long
  
  For i = 2 To RgActOsz
     For sor = 2 To ActSor
        Cells(sor, i).Value = Application.WorksheetFunction.Index(ws1.Columns(i),
        Application.WorksheetFunction.Match(Cells(sor, 1), Rg.Columns(1), 0))
     Next sor
  Next i
  
  End Sub
  </code></pre></li>
  </ol>