Index Match VBA code

[irfananeeza]

Dear Excel Experts, how can I make cell F2 dynamic? I have tried tweaking it as Match (range("F" & x), $B$2:$B$6,0),1)", OR Match( cells( x, 6), $B$2:$B$6,0),1)" but none worked. IS there a way to tweak the same very formula code to make it work.

Sub match_Index_Formula()

For x = 2 To Cells(Rows.Count, 6).End(xlUp).Row

Range("G" & x) = "=INDEX($C$2:$C$6, MATCH( F2, $B$2:$B$6,0),1)"

Next x

End Sub

 

[DanteAmor]

Try this

Sub match_Index_Formula()
  With Range("G2:G" & Range("F" & Rows.Count).End(3).Row)
    .Formula = "=INDEX($C$2:$C$6, MATCH(F2, $B$2:$B$6,0),1)"
  End With
End Sub

After reviewing the results, try the following, to leave only the values in the cells.

Sub match_Index_Formula()
  With Range("G2:G" & Range("F" & Rows.Count).End(3).Row)
    .Formula = "=INDEX($C$2:$C$6, MATCH(F2, $B$2:$B$6,0),1)"
    .Value = .Value
  End With
End Sub

 

[irfananeeza]

Dear expert DanteAmor, thank you for the code. It worked great without ForNext statement. When you have a moment could you please explain With Range line in your code. This part Range("G2":G" & Range("F" & ....) is unclear to me.
Also, just for learning only, is there a way to make F2 dynamic somehome in my original code. Please let me know. Thanks.

 

[irfananeeza]

I thought I understood, and if i write the code in this way it should work but it did not. I guess when you had a chance just help me understand. Would be appreciated. Thanks.

With Range("G2:G" & Rows.Count).End(3).Row

.Range("F" & Rows.Count).End(3).Row

.Formula = "=INDEX($C$2:$C$6, MATCH(F2, $B$2:$B$6,0),1)"

End With

End Sub

 

[DanteAmor]

could you please explain With Range

Range("G2:G" & Range("F" & Rows.Count).End(3).Row)

That means that it will work with the range from G2 to G and the last row with data but from column F, that is, if in the last row with data from column F it is 20, then the range goes from G2:G20.
If you put this: G2:G and the last row with data from the same column G, in this case, it won't work because column G is empty.

Also, just for learning only, is there a way to make F2 dynamic somehome in my original code

Try this

Sub match_Index_Formula2()
  Dim x As Long
  For x = 2 To Cells(Rows.Count, "F").End(xlUp).Row
    Range("G" & x).Formula = "=INDEX($C$2:$C$6, MATCH(F" & x & ", $B$2:$B$6,0),1)"
  Next x
End Sub

 

[irfananeeza]

Dear Excel Expert DanteAmor,

Please accept genuine thanks from my heart. You don't know how much I appreciated. Your explanation made sense and second variation in the code worked well as well.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.