Option Explicit
Sub ErsteLeereSpalteInklWeekday()
Dim wks As Worksheet
Dim rng As Range
Dim col As Object
Set wks = ThisWorkbook.Worksheets(1)
Set rng = wks.Range("I2406:NO2455")
For Each col In rng.Columns
With wks
If Not Weekday(.Cells(6, col.Column).Value, vbMonday) > 5 And IsError(IsHoliday(.Cells(6, col.Column).Value)) = True Then
If col.Cells.Count = Application.WorksheetFunction.CountBlank(col) Then
MsgBox "Erste leere Spalte im Bereich " & rng.Address & " : " & col.Column
Exit Sub
End If
End If
End With
Next
MsgBox "Keine leere Spalte im Bereich & " & rng.Address & " gefunden."
End Sub
Function IsHoliday(d As Date) As Variant
Dim v As Variant
v = Application.Match(CDbl(d), ThisWorkbook.Worksheets("Public Holidays").Columns(1), 0)
IsHoliday = v
End Function