CODEWITHSUNDEEP
vbaexcelloops
László Anga
László Anga

Reputation: 1

VBA: relative cell reference in loop

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.

 Error msg: Run-time error '1004':

Unable to get the Match Property of the WorksheetFunction class

Sub testing()

    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

Upvotes: 0

Views: 1335

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

Any time you use a Range() object there is an implicit ActiveSheet.Range() command used. IF the sheet you want to pull values from is not active the Range() command will give the wrong results.

My suggestions:

  1. Qualify all Range() functions with the appropriate worksheet object, like ws1.Range().
  2. Pick the top left cell in a table with a range, and then extend to the table using the Resize() command. For example to pick cell A2 and the next 100 rows and 20 columns, use ws1.Range("A2").Resize(100,20).
  3. Instead of using Cells() which also have an implicit active worksheet use an existing range and use the Offset() command. To pick the i-th row of a table with 20 columns starting from cell A2 use Range("A2").Offset(i-1,0).Resize(1,20)
  4. Use named ranges to pick cell references because they move with the cells. For example Range("AccountsTable").Offset(i-1,0).Resize(1,20)

Upvotes: 0

Pierre
Pierre

Reputation: 1046

Set Rg = ws2.Range("B2", Cells(ActSor, ActOsz))

this is forbidden. Correct syntax: 

Set Rg = Range( ws2.Range("B2"), ws2.Cells(ActSor, ActOsz))

Does that help?

Upvotes: 1

Related Questions