PDA

View Full Version : [SOLVED] VBA macro help to find index match error



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

p45cal
09-30-2014, 11:24 PM
Is the variable Index assigned its value outside this sub?

Kaniguan1969
10-01-2014, 05:31 PM
Inside the sub. do you have any other alternative codes instead of using evaluate? thanks for the reply.

p45cal
10-03-2014, 06:36 AM
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
10-03-2014, 03:54 PM
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!D2:D5000"

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
10-07-2014, 10:55 PM
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
10-08-2014, 01:07 AM
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
10-09-2014, 05:50 PM
Thanks snb. its possible this code will work in vba macro? Please see sample code in my thread.

snb
10-10-2014, 05:25 AM
It is a VBA macro.

Kaniguan1969
10-14-2014, 12:02 AM
Thank you snb.