VBA macro help to find index match error

[Kaniguan1969]

Hi I have a macro that use index match to find records from worksheet to another worksheet. I'm using 3 criteria to find the data.
Upon running the code i got an error. it say "Wrong number of arguments or invalid property assignment". this point to EVALUATE function.
May i ask your help on how i got the error or did you have any other idea. Thank you.

Sub UpdateStatus()
    Dim wb As Workbook
    Dim ws As Worksheet, wsSearchin As Worksheet
    
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Intransit_")
    Set wsSearchin = wb.Sheets("CoresStatus")
    
    If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
    ws.UsedRange.AutoFilter Field:=6, Criteria1:="IN-TRANSIT"
     
    'Change the Index and SearchIn ranges here..    Index = "CoresStatus!D2:D5000" contains "date" and "TBA"
    SearchIn = "CoresStatus!B2:B5000&" & _
               "CoresStatus!F2:F5000&" & _
               "CoresStatus!D2:D5000"
    For r = 2 To ws.Cells(Rows.Count, "A").End(xlUp).row
        If ws.Rows(r).Hidden = False Then
            SearchFor = Join(Array(ws.Cells(r, "A"), ws.Cells(r, "B"), ws.Cells(r, "C")), "")
            
            'got an error in this portion red color font
            status = Evaluate("INDEX(" & Index & ",MATCH(" & """" & SearchFor & """" & ",INDEX(" & SearchIn & ",),0))")
        
            If IsError(status) Then
                If status = CVErr(xlErrNA) Then ws.Cells(r, "F") = "IN-TRANSIT"
            ElseIf status = "Not Yet" Then
                Cells(r, "F") = "IN-TRANSIT"
            Else
                Cells(r, "F") = "RECEIVED"
            End If
        End If
    Next
End Sub

[p45cal]

Is the variable Index assigned its value outside this sub?

[Kaniguan1969]

Inside the sub. do you have any other alternative codes instead of using evaluate? thanks for the reply.

[p45cal]

Inside the sub. do you have any other alternative codes instead of using evaluate? thanks for the reply.

Probably. At the moment index is NOT being defined inside the sub. Could you attach a workbook with what you're wanting? It is not at all clear what you're searching for and where…

[SamT]

In order to compile youtr code, I had to make certain cvhanges and add some declarations. ( I had to add the declarations because I place "Option Explicit" at the top of all my code, to catch some common errors. (go to the Tools menu >> Options >> Editor and check "Require Variable Declaration.

    Dim Ndex As String ' I never use a reserved word as a Variable.
    Dim SearchIn As String
    Dim SearchFor As String
    Dim r As Long
    Dim Status As String

     'Change the Index and SearchIn ranges here..
     Ndex = "CoresStatus!D2:D5000" 'contains "date" and "TBA"


 Status = Evaluate("INDEX(" & Ndex & ",MATCH(" & """" & SearchFor & """" & ",INDEX(" & SearchIn & ",),0))")

Looking at the code I see that the String SearchIn evaluates to

"CoresStatus!B2:B5000&CoresStatus!F2:F5000&CoresStatus!D25000"

That the SearchFor = Join(etc) is the same as

With ws.Rows(r)
SearchFor = .Cells(1) & .Cells(2) & .Cells(3)
End With

Looking at this line

Status = Evaluate("INDEX(" & Ndex & ",MATCH(" & """" & SearchFor & """" & ",INDEX(" & SearchIn & ",),0))")

I will rewrite it to its very basic meaning

INDEX(Array(5000), MATCH(String, Array(15000)))

Do you see the basic flaw herein?

I understand why you are using all those confusing quotes and ampersands, but you really don't need to use Evaluate; INDEX and MATCH work just fine with Ranges and Strings. While I can't understand what the SearchIn Range is all about and what its relation to the (concatenated) values in Columns A, B, and C of Row "r" is, you can use code like

'Dim and Set Ndex and SearchIn as Ranges
     With Application.WorksheetFunction
        Status = .Index(Ndex, .Match(SearchFor, SearchIn), 0) 'An example only. Not correct for your purposes
    End With

[Kaniguan1969]

Thanks Samt. I used Index Match instead of using vba macro. Below is the formula.

=IF(B12288="","",IFERROR(IF(INDEX('[Cores Status Summary 2014.xlsx]Core Status'!$J:$J,MATCH($A12288&$B12288&$C12288,'[Cores Status Summary 2014.xlsx]Core Status'!$N:$N,0))="Done","RECEIVED","IN-TRANSIT"),"IN-TRANSIT"))

[snb]

A oneliner might suffice:

Sub UpdateStatus()
    [Intransit!F2:F5000] = [if(intransit_!F2:F5000="","",if(Intransit_!A2:A5000&Intransit_!B2:B5000&Intransit_!C2:C5000=CoresStatus!!B2:B5000&CoresStatus!!F2:F5000&CoresStatus!!D2:D5000,"IN-TRANSIT","RECEIVED"))]
End Sub

[Kaniguan1969]

Thanks snb. its possible this code will work in vba macro? Please see sample code in my thread.

[snb]

It is a VBA macro.

[Kaniguan1969]

Thank you snb.