CODEWITHSUNDEEP
vbafilter
Mats Olsson
Mats Olsson

Reputation: 101

VBA Filter function

What am I doing wrong here?

Dim Found() As Variant
Dim Found1 As Variant

Found = Application.Transpose(Application.Evaluate("=IF((ID=""" & sString1 & """)*(No=Value(""" & sString2 & """)),ROW(ID),""z"")"))

Found1 = Filter(Found, "z", False)

I have a good "Found" array filled with all "z":s and just one valid hit. But when I try to use the Filter function to rid the "z" out, I get a "mismatch" error?. Why?"

ID and No are "named ranges" ID is strings and No is numbers

Upvotes: 0

Views: 2528

Answers (2)

Mats Olsson
Mats Olsson

Reputation: 101

So this is where I am right now

Dim Found() As Variant

        Found = Application.Evaluate("=IF((ID=""" & sString1 & """)*(No=Value(""" & sString2 & """)),ROW(ID),""z"")")

        i = 1
        While Found(i, 1) = "z"
            i = i + 1
        Wend

If I cannot use Filter then there is no need to transpose either, I just iterate through the array till I find my match. I will always have just one! I will have up to 5000 rows to go through so timings will differ from case to case.

Upvotes: 0

Brad
Brad

Reputation: 12245

You do not want an array of variants you just want a variant which happens to be an array.

Dim Found() As Variant
Dim Found1 As Variant

Found = Application.Transpose([A1:A5])

Found1 = Filter(Found, "z", False)

Upvotes: 2

Related Questions