VBA - For Cycle with "On Error" not working on the second iteration

[Michele317]

Hello everyone,

as stated in the title of the thread, I'm having some problem with a VBA Code.
This is the code:

For i = 3 To 33

            If Cells(i, 4) = "Holiday" Then

                On Error GoTo nonParty:

                    tipoFestività = WorksheetFunction.VLookup(Cells(i, 2), sheet_set.Range("F10:F33"), 5, 0)

...

This part of the code checks first if a cell contains a certain value ("Holiday", in this case) and, if that's true, it does a VLookup of the date (that is in column 2) in the range F10:F33 of another sheet (the range are the holidays which are not saturday or sunday. So, I have a range of dates for Christmas, Easter, ...), giving back a certain string which can be "Half Holiday" or "Full day Holiday".
I need to know if it's a half day holiday or a full day holiday for formatting purposes.

The problem i'm having is the follow: The iteration with the For cycle seems to works fine, it skips some days (since they are not holidays) and then, when it gets to saturday, it execture the If statment and so: it does the Vlookup, it understands that the date is not in the holiday range (the one containing only Christmas, Easter, ...) and skips to the On Error part of the code.
When it start the new For Cycle iteration, it gives me back the following error "Unable to get the VLookup property of the WorksheetFunction class" which appear when it doesn't find any value. This is also correct, but why the code doesn't execute the On Error part of the code again?

Thank you all in advance

 

[RoryA]

You didn't show the nonParty section but I can guarantee you don't have a Resume statement of any kind there, which is needed to reset the current error state. It would be simpler to make tipoFestività a Variant, and use Application.Vlookup instead, which will return an error value that you can test with Iserror, rather than a run-time error that you need an error handler for:

For i = 3 To 33

            If Cells(i, 4) = "Holiday" Then

                    tipoFestività = Application.VLookup(Cells(i, 2), sheet_set.Range("F10:F33"), 5, 0)
                    If Iserror(tipoFestività) Then
                        ' not a holiday; handle appropriately
                    Else
                       ' it's a holiday
                    End If
              End If
Next i

 

[Michele317]

You didn't show the nonParty section but I can guarantee you don't have a Resume statement of any kind there, which is needed to reset the current error state. It would be simpler to make tipoFestività a Variant, and use Application.Vlookup instead, which will return an error value that you can test with Iserror, rather than a run-time error that you need an error handler for:

For i = 3 To 33

            If Cells(i, 4) = "Holiday" Then

                    tipoFestività = Application.VLookup(Cells(i, 2), sheet_set.Range("F10:F33"), 5, 0)
                    If Iserror(tipoFestività) Then
                        ' not a holiday; handle appropriately
                    Else
                       ' it's a holiday
                    End If
              End If
Next i

Ok, there are improvements :D And thanks a lot!
Now, no matter, but it always format as if it can't find the value.
I post the whole code:

        For i = 3 To 33
       
            If Cells(i, 4) = "Festivo" Then
               
                tipoFestività = Application.VLookup(Cells(i, 2), sheet_set.Range("F10:F33"), 5, False)
               
                If IsError(tipoFestività) Then
               
                    With Range("D" & i & ":S" & i)
               
                        .Merge Across:=True
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                       
                    End With
       
                Else
               
                    If tipoFestività = "Intera Giornata" Then
                   
                        With Range("D" & i & ":I" & i)
               
                            .Merge Across:=True
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                       
                        End With
               
                    ElseIf tipoFestività = "Mezza Giornata" Then
               
                        With Range("D" & i & ":E" & i)
               
                            .Merge Across:=True
                            .HorizontalAlignment = xlCenter
                            .VerticalAlignment = xlCenter
                       
                        End With
                   
                    End If
               
                End If
               
            End If
           
        Next

I have already tried to "play" with the If stament, ElseIf, etc, also by not creating another sub If but I get the same result

 

[RoryA]

You're trying to return a result from the 5th column of the Vlookup range, but that range is only one column, so it will always error.

 

[Michele317]

You're trying to return a result from the 5th column of the Vlookup range, but that range is only one column, so it will always error.

Yes, that was part of the problem indeed. I figured it out but I think there is a problem with how the range of dates are created (so the second argument of the VLookup function):
in fact, I create the dates in the range by concatenate the year. So, for example, 25/12/2024 is construct as =25/12/" & ColumnRow

Also the date to search (so the first argument of the VLookup function) are made by adding to the previous date +1.
So, for example:
A macro write "01/12/2024" (format: date) and then all the other dates are created by adding "+1" to the previous one.

I'm reading some stuff to get only the cell value (so the result) because I think this is why I get the error.

Also, to do some test, I print the value that i get from the VLookup and it takes just the few dates of holidays that i haven't made by concatenation.

Any idea on how to do this?

 

[RoryA]

So, for example, 25/12/2024 is construct as =25/12/" & ColumnRow

That will not create a date, it will create text. You should use the DATE function:

=DATE(ColumnRow,12,25)

or if you are doing it in VBA the function is DateSerial

 

[Michele317]

That will not create a date, it will create text. You should use the DATE function:

=DATE(ColumnRow,12,25)

or if you are doing it in VBA the function is DateSerial

Nice! Everything seems to work now Thank you so much!

I never used that function cause i thought the result would be YY/M/G and i need GG/MM/YYYY

Thank you again for all the help :D

 

[Joe4]

You can format the date to be in any date format you want.
In VBA, just use the FORMAT function. See here: MS Excel: How to use the FORMAT Function with Dates (VBA)

 

[Michele317]

You can format the date to be in any date format you want.
In VBA, just use the FORMAT function. See here: MS Excel: How to use the FORMAT Function with Dates (VBA)

Oh nice, didn't know about this. Thank you!

 

[Joe4]

You are welcome.