Type mismatch with multiple criteria Application.Match VBA

Question

I am trying to run a multiple criteria match function on VBA but keep getting a mismatch error despite setting the outcome variable place as Variant.

Sub AutoFactura()

Dim WS As Worksheet
Dim WF As Worksheet
Dim WP As Worksheet
Dim Rng As Range
Dim Codi As Range
Dim Ara As Range
Dim CopyRange As Range
Dim r1 As Range, r2 As Range
Dim i As Integer
Dim p As Integer
Dim PA As Range
Dim pacient As String, F As String
Dim place As Variant

Set WS = Sheets("Agenda")
'Desfiltrar Agenda
On Error Resume Next
WS.ShowAllData
On Error GoTo 0

'Definir worksheets i trobar últimes files i columnes de cada worksheet
Set WF = Sheets("Factura")
Set WP = Sheets("Pacients")
LRP = WP.Cells(Rows.Count, 1).End(xlUp).Row

'definir ranges per multiple criteria match
Set r1 = WS.Range("C2:C5000")
Set r2 = WS.Range("G2:G5000")
F = "F"


i = 1
p = 2

So now I want to get the row number of pacients that satisfies two conditions. I tried

 For p = 2 To LRP
    WP.Activate
    Range("L" & p).Select
    pacient = Range("B" & p)
    Do While Range("O" & p).Value = True
        With Application
            place = .Match(1, (r1 = pacient) * (r2 <> F), 0)
        End With
        If Not IsError(place) Then
        putuns = place + 1
        WS.Activate
        Range("J" & putuns).Select
        ActiveCell.FormulaR1C1 = "=IF(RC[-3]=""F"",0,1)"
        Range("J" & putuns + 1).Select
        ActiveCell.FormulaR1C1 = "=IF(AND(RC[-7] =R" & putuns & "C3, MONTH(RC[-8])=MONTH(R" & putuns & "C2),RC[-3] <> ""F""),1,"""")"
        Range("J" & putuns + 1).Select
        Selection.Copy
        Range("J" & putuns + 1 & ":J5000").Select
        ActiveSheet.Paste
        End If

But place is always a type mismatch. Any idea on how to solve this?

The message error writes (r1 = pacient) = 'type mismatch'. I am looking for a string in a range, but this should be fine, right?

Thank you

There appears to be some lines missing from the end of the code like `Loop` and `Next p` — CDP1802, Dec 17 '22 at 15:31
Yes, but it was not to post all the code. It is not the root of the problem — Foucault, Dec 17 '22 at 15:40
There is no Application.Match Function in my Excel There is a Application.WorksheetFunction.Match and this returns a double not a string — Red Hare, Dec 17 '22 at 17:29
@RedHare Application.Match doesn't appear with intellisense. There is a difference with Application.WorksheetFunction.Match - [see here](https://stackoverflow.com/questions/27302794/application-match-gives-type-mismatch/27302888#27302888) — CDP1802, Dec 17 '22 at 18:05
uups I always forget the deprecated? methods. Strangely it is not even shown in the object explorer even if hidden elements are shown. Yes you can get old as a cow but still learning :-) Tanks for the hint. — Red Hare, Dec 18 '22 at 10:34

Domenic · Accepted Answer · 2022-12-17T23:33:50.333

1

Try using the Evaluate method instead . . .

With Application
    place = .Evaluate("MATCH(1,(" & r1.Address(external:=True) & "=""" & pacient & """)*(" & r2.Address(external:=True) & "<>""" & F & """),0)")
End With

The string passed to the Evaluate method will resolve to something like this (depending on the workbook name, sheet name, and value returned by pacient)...

MATCH(1,([Book1.xlsm]Sheet1!$C$2:$C$5000="X")*([Book1.xlsm]Sheet1!$G$2:$G$5000<>"F"),0)

The Evaluate method then evaluates the string, and returns a value. Note that there's a 255 character limit. For additional information, have a look at the following link...

Application.Evaluate Method

edited Dec 17 '22 at 23:33

answered Dec 17 '22 at 18:04

Domenic

7,844
2
9
17

It works! Can you explain? Thank you so much – Foucault Dec 17 '22 at 22:35
1

I have added additional information to my post. Please see my edited post. – Domenic Dec 17 '22 at 23:35
I see, but then why a more traditional match like ```place = .Match(pacient, r1, 0)``` works and does not return the same mismatch error? – Foucault Dec 19 '22 at 15:42
That's because it's using the proper syntax. You have the lookup value `pacient`, the lookup array `r1`, and the match type `0`. The lookup array must be a range object or an array. – Domenic Dec 19 '22 at 16:04
1

In your original code, you had `(r1 = pacient) * (r2 <> F)` as your lookup array, which is neither a range object nor an array. It has to be evaluated in order to result in an array, which `application.match` cannot resolve. – Domenic Dec 19 '22 at 16:06
It's clear now, thank you! – Foucault Dec 19 '22 at 16:46

Type mismatch with multiple criteria Application.Match VBA

1 Answers1