VBA Index Match Function Help

[waveyurgi1]

Hello,

I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..

Here is the code:

Sub test1()

Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range

wsname = "CustomerIDbyParts"

i = 5
j = 6
Set r1 = ActiveWorkbook.Worksheets(wsname).range("D2:D4")
Set r2 = ActiveWorkbook.Worksheets(wsname).range("A2:A4")
Set r3 = ActiveWorkbook.Worksheets(wsname).range("B2:B4")
Set r4 = ActiveWorkbook.Worksheets(wsname).range("C2:C4")

myvalue = Evaluate("INDEX(r1, MATCH(ActiveSheet.Cells(5,1) & ActiveSheet(5,2) & ActiveSheet(1,4) ,r2 & r3 & r4, 0))")

ActiveSheet.Cells(i, j) = myvalue

End

The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?

I am wondering if anyone can help? Thank you.

 

[Marcelo Branco]

Hi,

Maybe this

Sub test11()
    Dim myvalue As Variant
    Dim wsname As String
    Dim i As Integer
    Dim j As Integer
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
    Dim r4 As Range
 
    wsname = "CustomerIDbyParts"
    i = 5
    j = 6
 
    With ActiveWorkbook.Worksheets(wsname)
 
        Set r1 = .Range("D2:D4")
        Set r2 = .Range("A2:A4")
        Set r3 = .Range("B2:B4")
        Set r4 = .Range("C2:C4")
 
        myvalue = Evaluate("=INDEX(" & r1.Address & _
            ",MATCH(""" & .Cells(5, 1) & .Cells(5, 2) & .Cells(1, 4) & """," _
            & r2.Address & "&" & r3.Address & "&" & r4.Address & ",0))")
 
        .Cells(i, j) = myvalue
 
    End With
 
End Sub

M.

 

[waveyurgi1]

Hello,

Thanks for the response.. I tried that Code, now it is giving me #N/A..

if I just tried the regular index and match function, it works, but i am having trouble with referencing ranges from other worksheet within the workbook...

 

[waveyurgi1]

I tried another code to see if the syntax for criteria cells from ActiveSheet is wrong.. the code is

i=5
j=6

ActiveSheet.Cells(i, j) = Evaluate("Index(M3:M5,Match(ActiveSheet.cells(5,1) & ActiveSheet.cells(5,2) & ActiveSheet.cells(1,4), J3:J5 & K3:K5 & L3:L5,0))")

and that gave me #NAME? for F5 .. so I guess my cell references for ActiveSheet critiera have problems too?

if I just do

ActiveSheet.Cells(i, j) = Evaluate("Index(M3:M5,Match(A5 & B5 & D1, J3:J5 & K3:K5 & L3:L5,0))")

It works, but I would like to point to other ranges from another worksheet in the workbook...

anyone have any ideas/suggestions? Thanks..