Match worksheet function in VBA (1 Viewer)

[Brianwarnock]

Match can be used to return the row number when searching in 2 column,( normally it would have Index wrapped round it.)

=MATCH(A1&B1,(Sheet3!D$1$7)&(Sheet3!F$1:F$7),0)
edit that Damn smilie should be : D

but I could not replicate this in VBA where I kept getting
Run Time error '13'
type mismatch
I coded

Dim myrow2 as Long
myrow2 = Application.WorksheetFunction _
 .Match(Sheets("sheet1").Range("A1") & Sheets("Sheet1").Range("B1"), Worksheets("sheet3").Range("d1:d7") & Worksheets("sheet3").Range("e1:e7"), 0)

anybody any ideas?

Brian

 

[lemo]

i would test for a few things -
1 - find value vs not find any - maybe it can't handle #N/A
2 - type of input - text or number or date etc - can be a problem with mismatch
3 - maybe it doesn't like '&'s ?

 

[Brianwarnock]

Thanks for the response.
The worksheet function works fine on the sheet so the data is ok, but it appears that although concatenating the columns is ok on the sheet it is not working for me in the VBA version, I was wondering if I was doing that bit wrongly, the & works fine for the search criteria.

I should point out that this is a point of interest not life and death , I solved the posters problem a different way.

Brian

 

[pwbrown]

Brian I don't believe you can't have two different arrays in vba although I'm probably wrong!
I tested the formula and it worked with one range/array and two lookups.

Dim myrow2 As Long
Dim tempnum As Long
tempnum = Sheets("sheet1").Range("A1") & Sheets("sheet1").Range("B1")
myrow2 = Application.WorksheetFunction _
 .Match(tempnum, Worksheets("sheet2").Range("A1:A10"), 0)

So for your example you could concatenate the D and E columns into F, it's a workaround if it helps.

 

[NBVC]

Also Brian,

The MATCH() function as you wrote it would be an Array Formula... not a normal formula....

You can try converting this formula to your VBA

=MATCH(1,INDEX(A1&B1=(Sheet3!D$1: D$7)&(Sheet3!F$1:F$7),0),0) which is still an array formula, but handled like a normal formula (i.e. no CTRL+SHIFT+ENTER confirmation necessayr)

i.e.

myrow2 = Application.WorksheetFunction _
.Match(1,Application.WorksheetFunction.INDEX(Sheets("sheet1").Range("A1") & Sheets("Sheet1").Range("B1")= Worksheets("sheet3").Range("d1:d7") & Worksheets("sheet3").Range("e1:e7"), 0),0)

 

[Brianwarnock]

Thank you all for your replies, I am now convinced that you cannot concatenate two columns in vba as you can on the worksheet as NBVC's formaula gave the same result.

I had not appreciated the fact that an array formula might not work in the vba environment as there is no mention of that in help, well I have not found it yet.

NBVC I had to change your formula to get it to work on the worksheet, replace the 1 with TRUE, else I got a #N/A

=MATCH(TRUE,INDEX(A1&B1=(Sheet3!D$1$7)&(Sheet3!F$1:F$7),0),0)

It was a neat trick, pity it could not be translated to VBA, the original poster who sparked this line of thought by me will have to use the Find Findnext and use of Offset that I coded to solve her problem.

Again thanks to all

Brian

 

[NBVC]

You're correct, it should have been TRUE... the 1 is used when there are multiple conditions...

Also, you should be able to use EVALUATE method for array formulas...

This is a google search result:

https://www.google.ca/search?q=eval...s=org.mozilla:en-US:official&client=firefox-a

 

[Brianwarnock]

Thanks, working that out should be intersting and keep me out of mischieve.

I'll post back when I've had a go.

Brian

 

[Brianwarnock]

Yippee it works

I canuse my original formula in F1 in the sheet where the code is

myrow2 = Evaluate(Range("F1").FormulaArray)

or NBVC which is in F4

myrow2 = Evaluate(Range("F4").Formula)

Thank you NBVC , I don't suppose that I will use this in anger so to speak as I'm 7 years retired but hey its great to learn.

Brian

 

[NBVC]

You are welcome.

I hope that I still have the willpower you have to do any hardcore learning after I am retired! I wanna be sitting in my own little beachfront property in Jamaica or Bahamas when I retire.... sipping my rum punch.

 

[Brianwarnock]

You are welcome.

I hope that I still have the willpower you have to do any hardcore learning after I am retired! I wanna be sitting in my own little beachfront property in Jamaica or Bahamas when I retire.... sipping my rum punch.

LOL

I spent 44 happy years in IT, and consider it more fun than doing crosswords or the like, but it doesn't stop me enjoying my Malts, I could do with more sun though, spent yesterday walking in gentle rain.

Brian