Type mismatch with multiple criteria Application.Match VBA-CodePudding

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

CodePudding user response：

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