(Solved) – VBA match function with multiple criteria

(solved)-–-vba-match-function-with-multiple-criteria

i am new here and this is my first question, also i dont speak english so my code (variables) is sometimes in dutch.
I have a workbook with multiple sheets (reservations, cottages, validator and schedule). Schedule needs to be filled in with the correct cottage for the reservation.

My question: I want the cottage_id to be returned with the match function. I need the cottage_id (Which is in column A of the cottagesheet), where the class is correct and where the cottage size is correct.
i’ve tried so much but nothing seems to work
i get error messages like ‘type mismatch’ and invalid procedure call or argument.
thankyou in advance!
My code:


Dim i As Integer
Dim c As Integer
Dim d As Integer
Dim numrows As Long
Dim laatstekolom As Long
Dim cottagerow As Variant
Dim class As Integer
Dim guests As Variant
Dim cottage_size As Integer
Dim som As Long
Dim somrng As Range
Dim resKlasse As Integer
Dim cottageId As Integer
Dim klasserij As Range
Dim maxpersrij As Range
Dim zoekklasse As Integer


Set roostersheet = Worksheets("rooster")
Set Reservationsheet = Worksheets("reservations")
Set Cottagesheet = Worksheets("cottages")
Set validatorsheet = Worksheets("validator")
Set lookupsheet = Worksheets("lookup")


roostersheet.Cells(1, 1) = "Cottage_id"

'datum uit reservationssheet naar header roostersheet
For i = 1 To 42
roostersheet.Cells(1, 2) = Reservationsheet.Cells(2, 2)
roostersheet.Cells(1, 2   i) = Reservationsheet.Cells(2, 2)   i
Next i

'cottageid uit cottagesheet naar 1e kolom roostersheet
For i = 1 To 819
roostersheet.Cells(2, 1) = Cottagesheet.Cells(2, 1)
roostersheet.Cells(i   2, 1) = Cottagesheet.Cells(2, 1)   i
Next i

'fixed in rooster plaatsen
numrows = Reservationsheet.UsedRange.Rows.Count
laatstekolom = roostersheet.UsedRange.Columns.Count
Resnr = validatorsheet.Range("A:A")

For i = 2 To numrows
    If Reservationsheet.Cells(i, 16).Value <> 0 Then
    cottagerow = Reservationsheet.Cells(i, 16).Value - 1
    validatorsheet.Cells(i - 1, 2).Value = Reservationsheet.Cells(i, 16).Value
    End If
    For d = 2 To laatstekolom
        If Reservationsheet.Cells(i, 2) = roostersheet.Cells(1, laatstekolom) Then
        Range(roostersheet.Cells(cottagerow, datumkolom), roostersheet.Cells(cottagerow, laatstekolom   Reservationsheet.Cells(i, 3).Value - 1)).Value = Reservationsheet.Cells(i, 1).Value
        End If
    Next d
Next i


'reserveringen eisen sum = 0
For class = 4 To 1 Step -1
    For i = 2 To numrows

guests = Reservationsheet.Cells(i, 4).Value
            'juiste cottagesize
    If guests = 1 Then
        cottage_size = 2
    ElseIf guests = 2 Then
        cottage_size = 2
    ElseIf guests = 3 Then
        cottage_size = 4
    ElseIf guests = 4 Then
        cottage_size = 4
    ElseIf guests = 5 Then
        cottage_size = 5
    ElseIf guests = 6 Then
        cottage_size = 6
    ElseIf guests = 7 Then
        cottage_size = 8
    ElseIf guests = 8 Then
        cottage_size = 8
    Else: cottage_size = 12
    End If

    zoekklasse = class
    lookupsheet.Cells(1, 1).Value = zoekklasse
    lookupsheet.Cells(1, 2).Value = cottage_size



        If Application.WorksheetFunction.sum(Reservationsheet.Cells(i, 6), Reservationsheet.Cells(i, 15)) = 0 And Reservationsheet.Cells(i, 5).Value = class And Reservationsheet.Cells(i, 4).Value = cottage_size Then
            Dim klasseKolom As Variant
            Dim SizeKolom As Variant
            Dim test As String
            Set klasseKolom = Cottagesheet.UsedRange.Columns(3)
            Set SizeKolom = Cottagesheet.UsedRange.Columns(2)

            ' cottageId = Application.Match(1, (klasseKolom = "&zoekklasse&") * (SizeKolom = "&cottage_size&"), 0)
            cottageId = Evaluate("MATCH(1, ('lookupsheet'!A1="""&klasseKolom&""") * ('lookupsheet'!A2 = """&SizeKolom&"""), 0)")
 'If Application.WorksheetFunction.sum(jjuyiReservationsheet.Cells(i, 6), Reservationsheet.Cells(i, 15)) = 0 And Reservationsheet.Cells(i, 5).Value = class and Then

            'validatorsheet.Cells(cottageId, 2).Value = cottagesheet.Cells(i, 1).Value   'invullen in validatorsheet
          'Else
          'validatorsheet.Cells(i, 2).Value = "x"
    End If

    'ElseIf som <> 0 Then

Next i
Next class
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *