VBA error - check to see if cell is empty

[j4ymf]

Good Morning

I was wondering how would i write a VBA that checks to see if there are any empty cells before it moves on with the macro.

Im looking for if D10 ( Jason Fletcher ) is not empty then check to see if E10, F10, G10, H10, I10, L10 are filled in, as so in for rows 11 to 24
then if frm D12 if the whole row is empty move on.

if there is an error create a message box
MsgBox "All field need to be filled in on the form?", vbOKOnly

hope im making sence

many thanks Jason

 

[Jon Peltier]

I wouldn't use VBA. I would use conditional formatting to highlight cells that need to be filled in.

The condition for cell E10, for example, would be =AND(LEN($D10)>0,LEN(E10)=0)

 

[j4ymf]

Thank you Jon Peltier

 

[mumps]

Jon's suggestion is the way to go but if you actually need a macro, try:

Sub CheckEmptyCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, srcRng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("D10:D24")
        If rng <> "" Then
            Set srcRng = Union(Range("E" & rng.Row & ":I" & rng.Row), Range("L" & rng.Row))
            If WorksheetFunction.CountA(srcRng) = 0 Then
                MsgBox ("All fields need to be filled in on the form in row " & rng.Row & "."), vbOKOnly
                Exit Sub
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub