CODEWITHSUNDEEP
vbaexcelexcel-formulawildcard
ksmit144
ksmit144

Reputation: 87

Using Wildcards in Index(Match) function

I am using an Index(Match()) function in my vba code that seems to be experiencing some issues. Because some of the searched criterion aren't exactly alike, I am trying to use a "*" wildcard operator around cell C2 to search for like items. Also, I do not want to simply place the formula I am using into cells with a Range("D2").Formula code that will cause auto-updating, therefore, I am experiencing issues around this as well.

Here is my code.

Range("D2") = Index('Future_220_140_MON'!$B$20:$AK$24, Match(" * "&C2&" * "&T2, 'Future_220_140_MON'!$B$1:$AK$1&'Future_220_140_MON'!$A$20:$A$24, 0))

Am I simply misformatting the code, or is there a particular way to call out wildcards when matching multiple criterion.

Upvotes: 2

Views: 4710

Answers (2)

Chrismas007
Chrismas007

Reputation: 6105

First, you should always define what you want your result to be stored as. By default, just writing Range("D2") will use the .Value property of the cell, but it is better to write Range("D2").Value. Also both of your functions are actually Application.WorksheetFunctions. Try this code:

Dim MatchResult As Double
MatchResult = Application.WorksheetFunction.Match(Conditions)
Range("D2").Value = Application.WorksheetFunction.Index(Conditions, MatchResult)

WorksheetFunction.Index Method MSDN

WorksheetFunction.Match Method MSDN

Upvotes: 1

Rory
Rory

Reputation: 34045

Try this:

    Range("D2").Value = Application.Index(Sheets("Future_220_140_MON").Range("B20:AK24"), _
Application.Match(Range("T2").Value, Sheets("Future_220_140_MON").Range("A20:A24"),0), _
Application.Match("*" & Range("C2").Value & "*", Sheets("Future_220_140_MON").Range("B1:AK1"),0))

Based on clarification, the code would need to be like this:

    Range("D2").Value = Application.Index(Sheets("Future_220_140_MON").Range("B20:AK24"), _
Application.Match(Range("T2").Value, Sheets("Future_220_140_MON").Range("A20:A24"),0), _
Application.Match(Mid$(Range("C2").Value, Instr(Range("C2").Value, "_") + 1), Sheets("Future_220_140_MON").Range("B1:AK1"),0))

Further edit: if you want to actually put a formula in place:

Range("D2").Formula = "=Index('Future_220_140_MON'!$B$20:$AK$24,Match(T2,'Future_220_140_MON'!$A$20:$A$24,0),Match(MID(C2,FIND(""_"",C2)+1,LEN(C2)),'Future_220_140_MON'!$B$1:$AK$1,0))

Upvotes: 2

Related Questions