CODEWITHSUNDEEP
excelvbaloopsmatch
EvA
EvA

Reputation: 75

How do I handle an error after match function

is there a way to handle an error in a loop I use a match function to match a cottage with the right size and class as it is reserved. But if there is no cottage_size available, the match function returns an error, after which I want to upgrade the cottage(cottage_size=cottage_size+1) and search for a match again..
My question is how do i go back to the match function after the error and after I upgraded the size..

 If som = 0 And iDklasse = class And iDpers = cottage_size Then


                        Set klasseKolom = cottagesheet.UsedRange.Columns(3)
                        Set SizeKolom = cottagesheet.UsedRange.Columns(2)

                        For k = 4 To 1 Step -1
                            For p = 2 To 12

                                cottageId = (Evaluate("MATCH(1,(" & klasseKolom.Address(External:=True) & "=" & zoekklasse & ")*(" & SizeKolom.Address(External:=True) & "=" & cottage_size & "),0)"))
                                If Not IsError(cottageId) Then
                                huisnr = cottageId
                                    If Application.CountIf(validatorsheet.Range("B:B"), huisnr) = 0 Then                               'cottage beschikbaarheid (gaat niet goed)
                                        validatorsheet.Cells(iD, 2).Value = cottagesheet.Cells(cottageId, 1).Value                      'invullen in validatorsheet
                                        stay = Reservationsheet.Cells(iD, 3).Value
                                        arrival_date = Reservationsheet.Cells(iD, 2).Value
                                        For datumkolom = 2 To laatstekolom
                                            If arrival_date = roostersheet.Cells(1, datumkolom).Value Then
                                                 'If Application.CountBlank(Range(roostersheet.Cells(huisnr, datumkolom), roostersheet.Cells(huisnr, datumkolom + stay - 1))) = Range(roostersheet.Cells(huisnr, datumkolom), roostersheet.Cells(huisnr, datumkolom + stay - 1)).Cells.Count Then

                                                    Range(roostersheet.Cells(huisnr, datumkolom), roostersheet.Cells(huisnr, datumkolom + stay - 1)).Value = Reservationsheet.Cells(iD, 1).Value
                                                End If
                                            'End If
                                        Next datumkolom
                                    End If
                                ElseIf IsError(cottageId) Then zoekklasse = zoekklasse + k And cottage_size = cottage_size + p And klasseKolom = klasseKolom + k And SizeKolom = SizeKolom + p
                                                    cottageId = (Evaluate("MATCH(1,(" & klasseKolom.Address(External:=True) & "=" & zoekklasse & ")*(" & SizeKolom.Address(External:=True) & "=" & cottage_size & "),0)"))
                                                    huisnr = cottageId                  'indien er geen match is??
                                End If
                            Next p
                        Next k

thanks in advance

Upvotes: 0

Views: 62

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Normally to run worksheet functions you would use the WorksheetFunction API.

Early-bound, Application.WorksheetFunction.Match gives you compile-time validation and idiomatic VBA runtime errors in case of mismatch (i.e. you can handle a mismatch with an On Error statement).

Late-bound, Application.Match loses compile-time validation, but now you get a Variant/Error result instead of a VBA runtime error in case of mismatch.

Using the late-bound version, you would have to validate that the result is usable before you consume it. The IsError standard library function returns true given a Variant/Error argument, so here:

If IfError(cottageId) Then
    '...
End If

Try changing it to:

If IsError(cottageId) Then
    '...
End If

That makes your control flow look something like this:

For i = 1 To iterations
    Dim result As Variant
    result = Evaluate("complicated Excel formula string")
    If Not IsError(result) Then
        'TODO process valid result here
        Exit For 'we're done, no need to keep looping
    End If
Next

Consider heeding Darren's advice though: it does seem Range.Find could be a better tool here. Loop logic remains the same: last thing you want is to GoTo-jump and increment i until the counter overflows its data type - with a For loop you can cap the maximum number of attempts beyond which you just gotta admit that you didn't get a match for the given cottage_size; Range.Find/Range.FindNext have mechanisms that kind of already implement this for you.

Upvotes: 3

Related Questions