Verifying all data in each row is filled in
 

OK, so I have a spreadsheet with 14 columns of data that need to be
filled in. I need to verify that all the data in the spreadsheet for
each row has been filled in. So I know that I can get the last
physical row filled in with anything:

towhere = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row

So can use that to loop through. What I'm looking to do is say, if
only 13 of the columns have been filled in for a particular row, place
invalid in the 15th column.

There is a catch though, if the 13th column has a particular value in
it, say ".done", then the 14th column is not required. Not sure
exactly how to go about this as I wasn't sure if there was a simple
way for starters to get a fast count of the number of elements filled
in with data in a given row and if 14 we are good, if 13, check the
relation between column 13 and 14. If 12 or less just give the error.
Not sure if this is the correct approach or what. Any assistance
would be appreciated. Also keep in mind I do not care about any data
in other columns outside that area (i.e. 15 or up), so need a count or
however it should be done of fields filled in before that. Thanks.

JR

Verifying all data in each row is filled in
 

John,
This is how I'd do it. Maybe there's a better way though.

Sub CheckForMissingData()
Dim x, y, towhere As Integer
Dim isOK, isDone As Boolean

towhere = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row
isOK = True

'Check the first 12 columns...
For y = 1 To 12
For x = 1 To towhere
If ActiveSheet.Cells(x, y) = "" Then
isOK = False
Exit For
End If
Next x
If Not isOK Then Exit For
Next y

'Check column 13, look for "done"...
If isOK Then
isDone = False
For x = 1 To towhere
If ActiveSheet.Cells(x, 13) = "" Then
isOK = False
Exit For
ElseIf ActiveSheet.Cells(x, 13) = "done" Then
isDone = True
End If
Next x
End If

'Check column 14 if necessary...
If isOK And Not isDone Then
For x = 1 To towhere
If ActiveSheet.Cells(x, 14) = "" Then
isOK = False
Exit For
End If
Next x
End If

If isOK Then
MsgBox "All cells have been filled.", vbExclamation, "Complete"
Else
MsgBox "Not all cells are filled in.", vbExclamation, "Incomplete"
End If
End Su

--
Message posted from http://www.ExcelForum.com