VBA - validating data before proceeding

[s_whitie]

Hi

I have the following routine to print from my worksheet:

Sheets("BACS Ltr").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data Input").Select
Range("C5").Select

What I want is that if the user has not entered data into the following fields then the process should stop and a message displayed to advise the user:

"Please complete all data before proceeding"

Fields to be completed (all in sheet "Data Input")
C5
C18
C19
C27
G29

Can anyone help? Thanks

 

[Jonmo1]

You could put

IF Application.Countblank(Range("C5,C18,C19,C27,G29")) = 5 Then
    MsgBox "Please Complete..."
    Exit Sub
End If
 
continue code

Hope this helps..

 

[s_whitie]

Thanks

So now I have

If Application.CountBlank(Range("C5,C18,C19,C27,G29")) = 5 Then
MsgBox "Please Complete..."
Exit Sub
End If
Sheets("BACS Ltr").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data Input").Select
Range("C5").Select
End Sub

But this is giving me an error - Run Time error '13' Type mismatch??

 

[Jonmo1]

Wait, countblank didn't work the way I thought it would. It errors..

try this instead

Sub Test()
Dim myrange
Dim C As Range
Set myrange = Range("C5,C18,C19,C27,G29")
For Each C In myrange
    If Len(C) = 0 Then
        MsgBox "Please complete.."
        Exit Sub
    End If
Next C
Continue Code
End Sub

 

[s_whitie]

THanks that works now