具有多个条件的VBA匹配功能

我是新来的,这是我的第一个问题,我也不讲英语,所以我的代码(变量)有时在荷兰。 我有一本包含多个工作表的工作簿(预订,平房,验证器和时间表)。时间表需要填写正确的小屋以进行预订。

我的问题:我希望使用match函数返回Cottage_id。我需要Cottage_id(在小屋表的A列中),在该类中正确,在该小屋中尺寸正确。 我已经尝试了很多,但似乎无济于事 我收到诸如“类型不匹配”和无效的过程调用或参数之类的错误消息。 先感谢您! 我的代码:


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
评论
  • 弭希彤
    弭希彤 回复

    尝试以下...

    cottageId = Evaluate("MATCH(1,(" & klasseKolom.Address(External:=True) & "=" & zoekklasse & ")*(" & SizeKolom.Address(External:=True) & "=" & cottage_size & "),0)")
    

    然后您可以测试是否有匹配项,如下所示...

    If Not IsError(cottageId) Then
        MsgBox cottageId, vbInformation
    Else
        MsgBox "cottageId not found!", vbExclamation
    End If