PDA

View Full Version : Solved: please check second part of mu code



BENSON
01-16-2008, 01:17 AM
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


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

Andy Pope
01-16-2008, 01:46 AM
How about testing the cell for being empty.


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)
If Len(Cell1.Value) > 0 Then
' something to compare
'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
End If
Next Cell1

BENSON
01-16-2008, 02:05 AM
Perfect thanks for quick reply