The code below when activated will list any public holidays that a user should be made aware of after he has selected certain dates.It works fine if it detects a public holiday. My problem is that it will not return the MsgBox "No holidays in list " if none are present it will show the MsgBox V_message, vbInformation, " PUBLIC HOLIDAYS INCLUDED IN YOUR SELECTION" but the form will be blank thanks for any help
[VBA]
Sub Get_Dates()
Application.ScreenUpdating = False
Dim Cell As Range
Dim Cell1 As Range
Dim V_message As String
For Each Cell1 In Worksheets("DAILY SALES PROJECTION").Range("A10:A22,d10:d22,g10:g22,a25:a37,d25:d37,g25:g37,a40:a52 " & _
Worksheets("DAILY SALES PROJECTION").Range("A" & Rows.Count).End(xlUp).Row)
'sheet 2 starts at row 2 with dates in column A
'and description in column B
For Each Cell In Worksheets("Sheet2").Range("A2:A" & _
Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row)
If Cell Like "*" & Cell1.Value Then
V_message = V_message & Cell1.Text & " " & " " & Cell.Offset(0, 1).Value & vbCrLf & vbCrLf
Exit For
End If
Next Cell
Next Cell1
If V_message = vbNullString Then
MsgBox "No holidays in list !", vbInformation, "Holiday check."
Else
MsgBox V_message, vbInformation, " PUBLIC HOLIDAYS INCLUDED IN YOUR SELECTION"
Application.ScreenUpdating = True
End If
End Sub
[/VBA]
Reply With Quote
