1

My code runs but MATCH('Worksheets1'!$D2, is not changing or updating based on the the row it is at.

For example if my worksheet contains 2000 rows to be index matched the formula will still just contain MATCH('Worksheets1'!$D2 as the cell it is referencing .

How do I make the cell value change based on what what row number the code is at?

Sub trial()
On Error Resume Next

Dim Dept_Row As Long
Dim Dept_Clm As Long

Table1 = Worksheets1.Range("D:D") 
Table2 = Worksheets2.Range("A:B") 
Dept_Row = Worksheets1.Range("A2").Row 
Dept_Clm = Worksheets1.Range("A2").Column

For Each cl In Table1

    If IsEmpty(cl) Then Exit Sub
                                            
    If Not IsEmpty(cl) Then Worksheets("Worksheets1").Cells(Dept_Row, Dept_Clm) = _
      "=INDEX('Worksheets2'!$B:$B,MATCH('Worksheets1'!$D2,'Worksheets2'!A:A,0)) "

    Dept_Row = Dept_Row + 1

Next cl

Exit Sub

I created the loop in hopes of the formula updating giving it the

Dept_Row = Dept_Row + 1.

I tried both leaving it where it is, and introducing the code to quotation marks after the formula ended. However, my code is only able to run as is.

I was thinking of doing plus 1 but I doubted that would work it would just turn the cell value of D2 into D3 for all.

Community
  • 1
  • 1
Alexis Marquez
  • 21
  • 4
  • 1
    That `On Error Resume Next` is hiding some errors. – BigBen Nov 01 '22 at 17:59
  • can you explain a bit more on what you mean? I deleted that part and code still ran and tried to debug to see if there were any errors after that portion of the code was delete and I didn't get any just wanted to ask more to check. – Alexis Marquez Nov 01 '22 at 18:34
  • 1
    You're missing some `Set`: `Set Table1 = Worksheets1.Range("D:D")` and `Set Table2 = Worksheets2.Range("A:B")`. – BigBen Nov 01 '22 at 18:37
  • 1
    You mean `"=INDEX('Worksheets2'!$B:$B,MATCH('Worksheets1'!$D" & Dept_Row & "'Worksheets2'!A:A,0))"`? – findwindow Nov 01 '22 at 18:51
  • @findwindow i'd post that as an actual answer so this can be adequately closed... that should be the objective solution, allowing this to be "answered." – Cyril Nov 01 '22 at 18:55

2 Answers2

2

There's no need to loop. All of your code can be boiled down to:

With Worksheets("worksheet1")
    Dim lastRow As Long
    lastRow = .Range("D" & .Rows.Count).End(xlUp).Row
 
    .Range("A2:A" & lastRow).Formula = "=INDEX('Worksheets2'!$B:$B,MATCH(D2,'Worksheets2'!A:A,0))"
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40
0

Question is unclear but I guess this is what you're looking for:

"=INDEX('Worksheets2'!$B:$B,MATCH('Worksheets1'!$D" & Dept_Row & "'Worksheets2'!A:A,0))"
findwindow
  • 3,133
  • 1
  • 13
  • 30
  • Sorry let me see if I can clarify it I am trying to for example if the loop code is in row 1000 the D2 should be D1000 so I am trying to come up with a way to get that to happen. Using your suggestion render the code unable to run :( sorry but thank you for your suggestion :). – Alexis Marquez Nov 01 '22 at 19:08