PDA

View Full Version : Solved: check for empty cells



jazznaura
02-01-2008, 10:36 AM
hi all,

need help with my macro below. i can't figure out why it wont work.
it checks a number of cells to ensure they are not empty.

it works fine if the value of "err2" is anything but "N", but if the value is "N" it fails to prompt user to "Please Complete All Feilds" and seems to jump to select range a1.

please help i've been trying to figure this out all afternoon and i just can't see it.

thanks,


Sub BLANKS()

Dim Cell As Range
Dim Source As Range

Sheets("input").Unprotect Password:="**********"

If Range("err2") = "N" Then
Set Source = Sheets("input").Range("WeekNumber, dates, names, shift, apma, reason, tpnd, description, tihi, qtyad, cash, selqty, sysqty, res, dsr, goods, reshis, adjhis, selhis, textbox, errofgen")
Else
Set Source = Sheets("input").Range("WeekNumber, dates, names, shift, apma, reason, tpnd, description, tihi, qtyad, cash, selqty, sysqty, res, dsr, goods, reshis, adjhis, selhis, textbox, errofgen, genhan, sir")

If Application.WorksheetFunction.CountA(Source) < Source.Cells.Count Then
For Each Cell In Source.SpecialCells(xlCellTypeBlanks)
On Error Resume Next
Response = MsgBox("Please Complete All Feilds ", (vbInformation))
If Response = vbOK Then
Sheets("input").Protect Password:="***********"
Exit Sub
End If
Next
End If
End If
Set Source = Nothing
Range("a1").Select
End Sub

jazznaura
02-01-2008, 10:47 AM
SORRY GUYS,

i got it.