Evaluate index match with dynamic range

[inddon]

Hello There,

I have attached a sample workbook which returns a value, based on 2 search criteria cell value. This is done via for loop

I came across a way to do this via Evaluate (Index, Match).

I have attached a sample workbook for your reference (which is working on hard coded range). The explanation is given in the VBA Sub procedure.
I would like to make this work in a more dynamic way.

I tried various ways, but couldn't get it working. Could you please assist in how this can be achieved?

Below sample VBA code and the code change wish (from the sample workbook):

Sub Tester()
  Dim WsSheets As Worksheet
  Dim v1, v2, myValue As String, LastRowSheet1 As Long
    
  Set WsSheets = Sheets("Sheet1")
  LastRowSheet1 = WsSheets.Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To LastRowSheet1
    v1 = Cells(i, 5).Value
    v2 = Cells(i, 6).Value

    ActiveWorkbook.Names.Add Name:="X", RefersTo:=v1
    ActiveWorkbook.Names.Add Name:="Y", RefersTo:=v2

    'The below statement works well for me
    'Instead of giving a hardcode range, I would like to make this more dynamic
    myValue = Evaluate("INDEX($C$2:$C$5,MATCH(X & Y,$A$2:$A$5&$B$2:$B$5,0))")
    
    '--------------------------------------------------------
    'In the Evaluate INDEX function, instead of $C$2:$C$5, $A$2:$A$5 and $B$2:$B$5
    'I would like to use the variable LastRowSheet1 as:
    '--------------------------------------------------------
    ' "$C$2:$C$" & LastRowSheet1
    ' "$A$2:$A$" & LastRowSheet1
    ' "$B$2:$B$" & LastRowSheet1
    'myValue = Evaluate("INDEX($C$2:$C$ & LastRowSheet1,MATCH(X & Y,$A$2:$A$ & LastRowSheet1&$B$2:$B$ & LastRowSheet1,0))")

    '--------------------------------------------------------
    ' Or much better define a range
    '--------------------------------------------------------
    'lRngGetValue = WsSheets.Range("$C$2:$C$" & LastRowSheet1)
    'lRngCriteria1 = WsSheets.Range("$A$2:$A$" & LastRowSheet1)
    'lRngCriteria2 = WsSheets.Range("$B$2:$B$" & LastRowSheet1)
    'myValue = Evaluate("INDEX(lRngGetValue,MATCH(X & Y,lRngCriteria1&lRngCriteria2,0))")
    
    WsSheets.Cells(i, "G").Value = myValue
  Next i
End Sub

Look forward to hearing from you.

Regards,
Don

 

[Fluff13]

How about

Sub Tester()
  Dim WsSheets As Worksheet
  Dim v1, v2, myValue As String, LastRowSheet1 As Long
  
  Set WsSheets = Sheets("Sheet1")
  LastRowSheet1 = WsSheets.Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To LastRowSheet1
    v1 = WsSheets.Cells(i, 5).Value
    v2 = WsSheets.Cells(i, 6).Value

    ActiveWorkbook.Names.Add Name:="X", RefersTo:=v1
    ActiveWorkbook.Names.Add Name:="Y", RefersTo:=v2

    'The below statement works well for me
    'Instead of giving a hardcode range, I would like to make this more dynamic
'    myValue = Evaluate("INDEX($C$2:$C$5,MATCH(X & Y,$A$2:$A$5&$B$2:$B$5,0))")
    
    '--------------------------------------------------------
    'In the Evaluate INDEX function, instead of C5, A5 and B5
    'I would like to use the variable LastRowSheet1 as:
    '--------------------------------------------------------
    ' "$C$2:$C$" & LastRowSheet1
    ' "$A$2:$A$" & LastRowSheet1
    ' "$B$2:$B$" & LastRowSheet1
    'myValue = Evaluate("INDEX($C$2:$C$ & LastRowSheet1,MATCH(X & Y,$A$2:$A$ & LastRowSheet1&$B$2:$B$ & LastRowSheet1,0))")

    '--------------------------------------------------------
    ' Or much better define a range
    '--------------------------------------------------------
    lRngGetValue = WsSheets.Range("$C$2:$C$" & LastRowSheet1).Address
    lRngCriteria1 = WsSheets.Range("$A$2:$A$" & LastRowSheet1).Address
    lRngCriteria2 = WsSheets.Range("$B$2:$B$" & LastRowSheet1).Address
    myValue = WsSheets.Evaluate("INDEX(" & lRngGetValue & ",MATCH(X & Y," & lRngCriteria1 & "&" & lRngCriteria2 & ",0))")
    
    WsSheets.Cells(i, "G").Value = myValue
  Next i
End Sub

 

[inddon]

How about

Thank you very much Fluff13

Regards,
Don

 

[Fluff13]

You can also get rid of the named ranges as well if you want

Sub Tester()
  Dim WsSheets As Worksheet
  Dim v1, v2, myValue As String, LastRowSheet1 As Long
 
   Set WsSheets = Sheets("Sheet1")
   LastRowSheet1 = WsSheets.Cells(Rows.Count, 1).End(xlUp).Row
   lRngGetValue = WsSheets.Range("$C$2:$C$" & LastRowSheet1).Address
   lRngCriteria1 = WsSheets.Range("$A$2:$A$" & LastRowSheet1).Address
   lRngCriteria2 = WsSheets.Range("$B$2:$B$" & LastRowSheet1).Address

  For i = 2 To LastRowSheet1
    myValue = WsSheets.Evaluate("INDEX(" & lRngGetValue & ",MATCH(" & Cells(i, 5).Address & " & " & Cells(i, 6).Address & "," & lRngCriteria1 & "&" & lRngCriteria2 & ",0))")
    
    WsSheets.Cells(i, "G").Value = myValue
  Next i
End Sub

 

[inddon]

Thank you Fluff13. This is also new and good to know.

For understanding purpose, I suppose for me your previous solution is good.

Regards,
Don

 

[Fluff13]

You're welcome & thanks for the feedback.