VBA: relative cell reference in loop

Question

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

you need to trap errors in case you will not have a successful Match — Shai Rado
– Shai Rado, Commented Dec 27, 2016 at 15:20
Thanks for the help. The 2 sheets are identical and it is only created for testing purposes, which means that all values can be found on both sheets. Do I need to trap errors in this case as well? — László Anga
– László Anga, Commented Dec 27, 2016 at 15:26
not really, just if you have some logic error somewhere in your code. In my code I trap it anyway, in case I have an extra space at the end of the text, or some other fault I might have done — Shai Rado
– Shai Rado, Commented Dec 27, 2016 at 15:28
Why are doing Cells.Value instead of just Cells = ? You're getting the error because the match is failing though. — Liss
– Liss, Commented Dec 27, 2016 at 15:28
Thanks for the help. I have removed the .value and still getting the same error msg. — László Anga
– László Anga, Commented Dec 27, 2016 at 15:45

Pierre · Accepted Answer · 2016-12-27 15:33:22Z

1

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?

answered Dec 27, 2016 at 15:33

Pierre

1,0467 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

László Anga Over a year ago

Thanks for the help. Nope, still the same err msg.

John Alexiou · Accepted Answer · 2016-12-27 16:29:23Z

0

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:

Qualify all Range() functions with the appropriate worksheet object, like ws1.Range().
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).
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)
Use named ranges to pick cell references because they move with the cells. For example Range("AccountsTable").Offset(i-1,0).Resize(1,20)

edited Dec 27, 2016 at 16:29

answered Dec 27, 2016 at 16:12

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

3 Comments

László Anga Over a year ago

Thanks for the help @Ja72 The users can modify the original sheet - can add more columns - that is why I would not like to use the offset or the resize bacause that way I would need to modify the code each time. Is it possible to write a loop code that uses vloopup or index&match - I know that errror trapping is still missing - where it checked the value in column A and then brings back the values of the column, and if there are 10 columns it brings back the 10th column value and if there are 100, then the 100th?

John Alexiou Over a year ago

The way to deal with this it to use Named Ranges such that you never reference absolute locations, but always relative. I will edit my answer.

László Anga Over a year ago

I will see if I can resolve it this way. Thanks for the tip.

Collectives™ on Stack Overflow

VBA: relative cell reference in loop

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related