CODEWITHSUNDEEP
excelvba
Kurt
Kurt

Reputation: 135

Index match function VBA with 2 matches

What I am trying to do is to convert the following simple formula:

INDEX(Data!$E:$E,MATCH(1,($A4=Data!$B:$B)*(J$3=Data!$D:$D),0))

Into VBA code:

Sub MyTest()
Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range, MatchSecond As Range

Set destinationWs = ThisWorkbook.Worksheets("MyTest")
Set dataWs = ThisWorkbook.Worksheets("Data")

destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row


Set IndexRng = dataWs.Range("E2:E" & dataLastRow)
Set MatchRng = dataWs.Range("B2:B" & dataLastRow)
Set MatchSecond = dataWs.Range("D2:D" & dataLastRow)

For x = 2 To destinationLastRow

On Error Resume Next
'=INDEX(Data!$E:$E,MATCH(1,($A4=Data!$B:$B)*(J$3=Data!
a = Application.Match(1, destinationWs.Range("A" & x).Value, MatchRng, destinationWs.Range("C1").Value, MatchSecond, 0)
MyValue = Application.Index(IndexRng, a)
destinationWs.Range("C" & x).Value = MyValue

$D:$D),0))
On Error GoTo 0

Next x

End Sub

When I run the query the result is "No".

Could someone point me towards the right direction?

Thanks

Upvotes: 0

Views: 291

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

This ($A4=Data!$B:$B)*(J$3=Data!$D:$D) does not work in VBA without looping.

I recommend to use Evaluate:

RetVal = destinationWs.Evaluate("INDEX(Data!$E:$E,MATCH(1,($A" & x & "=Data!$B:$B)*(J$3=Data!$D:$D),0))")

Upvotes: 1

Related Questions