VBA: Using Array Formula

Question

I want to find a row number based on two criteria, in column C and E. My data looks like this:

I have googled my problem and using the Match function as an array formula works for this (worked when I used it in Excel, not VBA), but I can't figure out how to make it an array formula in VBA. Different solutions, be it using "[]" or .Evaluate didn't work for me (maybe that was my mistake, though). So how would I modify this code to get the result I want:

Sub Test1()
    Dim rowDB As Long
    Dim wsDB As Worksheet
    
    Set wsDB = ActiveSheet
    rowDB = WorksheetFunction.Match(CDate("30.06.2020") & "EX0500-0001", wsDB.Range("C7:C366") & wsDB.Range("E7:E366"))
End Sub

The error I get is "error 13: type mismatch", so I'm not sure if there's another issue here or just the lack of an array formula.

garbb · Accepted Answer · 2020-10-18 21:16:30Z

1

I played with this for a bit and found several problems:

It seems that CDate() doesn't like "30.06.2020" as input and gets a type error. It seems to be happy with "30-06-2020" so maybe use that format instead or just search for string "30.06.2020" instead? This should be ok as long as all of the date formats are consistent.

The WorksheetFunction.Match() second parameter must be a contiguous range and yours is not. Also I don't think the expression wsDB.Range("C7:C366") & wsDB.Range("E7:E366") makes sense; if you want to combine ranges use the Union() function. But this will not work here because as mentioned the range is not contigous.

I don't think it is possible to use WorksheetFunction.Match() to search for multiple values, so you might have to search for the date in coulmn C and the string in column E separately.

Here is some vba I got working for just searching for one value:

Sub Test4()
    Dim rowDB As Long
    Dim wsDB As Worksheet

    Set wsDB = ActiveSheet
    rowDB = WorksheetFunction.Match("30.06.2020", wsDB.Range("C7:C366"))
    Debug.Print rowDB
End Sub

Also, If a match is not found, it will get a "Application-defined or object-defined error" so you will need to implement some error handling.

edited Oct 18, 2020 at 21:16

answered Oct 18, 2020 at 21:10

garbb

6995 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

Thanks for your help.I'm not sure about the VBA function, but in Excel you can use Matchwith multiple criteria: in my German Excel version, =VERGLEICH(DATWERT("30.06.2020") & "EX0500-0001"; C7:C366 & E7:E366;0) works for me, which in the English version should be =MATCH(DATVALUE("30.06.2020") & "EX0500-0001", C7:C366 & E7:E366,0) As for looking separately through the columsn: would that mean two For-Each-Loops? Both columns have duplicate values, e.g. EX0500-0001 for more than one date, so something like .Find() wouldn't help, right?

garbb Over a year ago

I was messing around with this some more, as for CDate(): it appears that I was getting the type error due to the regional date format of windows. When I changed it to D-M-Y it worked ok. I could not get the excel formula to work for me, I don't know why. As for the vba: use a For loop to check each cell in the C column to see if it matches the date value, if you find a match for that row, then check if the value in the E column matches your other value: if it matches then you are done, if not check next cell in C column, etc...

Collectives™ on Stack Overflow

VBA: Using Array Formula

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related