Excel VBA/Macro - Search against Multiple Criteria

[kriskros07]

Hi, I am in dire need of some assistance. I have scoured the existing posts but cant quite find the right post to twig with me what i need to do.

I am trying to write a macro which will search on 2 pieces of data taken from e.g. Row1, i.e. cells A1 and B1 and match those to 2 pieces of data (found together) in another part of the spreadsheet e.g. these might be in cells N7 and O7. I want the code to return the position of the matched data (presumably using Match) - so this would be 7 if it matched the data with cells N7 and O7(it could be matched anywhere in these columns of course). A seperate issue - but just to explain why i am trying to do this - I will subsequently use this positioning to compare the remaining columns of data (e.g. compare in this instance A1:M1 against N7:Z7).

A Single criteria is easy enough:
where a is the value found in cell A1
relpos = Application.Match(a, Range("N:N"), 0) 'single criteria

however, i need to add in cells B1 to the reference argument and Range O:O to the lookup array.

I believe i need to use the Evaluate function also to do this in VBA. And Index maybe??

Any help will be very much appreciated!



Kind Regards,
Chris

[mikerickson]

you could use a CSE formula with Evaluate
[VBA]Sub test()
Dim sTerm1 As Variant, sTerm2 As Variant
Dim CSEFormula As String
sTerm1 = "b": sTerm2 = "y"
With Sheet1
CSEFormula = ("Match(" & Chr(34) & sTerm1 & Chr(34) & "&CHAR(5)&" & _
Chr(34) & sTerm2 & Chr(34) & "," _
& .Range("C1:C100").Address(, , , True) & "&CHAR(5)&" _
& .Range("E1:E100").Address(, , , True) & ",0)")
MsgBox Evaluate(CSEFormula)
End With
End Sub[/VBA]

[kriskros07]

CSE Formula??? Unfortunatley this means nothing to me. I think im going to look into concatenation of reference data and arrays.

Thanks for trying!

[GTO]

Cross-posted at: http://www.mrexcel.com/forum/showthread.php?t=575936


Greetings Chris,

Please see Here

[mikerickson]

CSE-"Ctrl-Shift-Enter"

I think we are on the same idea. If you wanted a formula for a cell, my approach would be like

=MATCH("A"&"~"&"B", C1:C100&"~"&E1:E100, 0)

Entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

The Evaluate expression above is how I would evaluate that in VBA.

[trhenkel]

I found this thread when trying to code a search on multiple criteria. I was able to get mikerickson's 09-01-2011, 09:57 AM code to work for me, but I don't understand what &CHAR(5)& does.
What is the purpose of that code snippet and why is it necessary in the formula?
Thanks.

[SamT]

https://duckduckgo.com/?t=palemoon&q...R(5)%26&ia=web

Nine Year old thread closed