Kaniguan1969
09-30-2014, 10:36 PM
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
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