Index Match VBA Formula Not Updating to Correct Row-CodePudding

Hello Stackoverflow community,

I am dealing with a VBA issue my code below works but I am running into the issue that this portion of the code "MATCH('Worksheets1'!$D2," is not changing or updating based on the the row it is at for example if my worksheet contains 2000 rows that need to be index matched the formula will still just contain MATCH('Worksheets1'!$D2 as the cell it is referencing was wondering if anyone knows how to 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 currently at and introducing the code to quotation marks after the formula ended. However, my code is only able to run how the current code 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.

Any advice would be greatly appreciated it.

Thank you

CodePudding user response：

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

CodePudding user response：

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))"