Is there a simple code to search a known range to verify
that each cell has correct data? The catch here is that
I always use columns A thru H, but do not know how many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header), then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.

Jeff

one way:

Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long

MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)

ExpectedCells = WorksheetFunction.CountA(Range("A1:H" & MaxRow))

If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If

End Sub

Regards

Trevor


"Jeff Green" wrote in message
...
Is there a simple code to search a known range to verify
that each cell has correct data? The catch here is that
I always use columns A thru H, but do not know how many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header), then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.

That is a good suggestion. It made me think

how about
if application.countA(Range("A:H")) mod 8 < 0 then
msgbox "Missing Data"
End if

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote in message
...
Jeff

one way:

Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long

MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)

ExpectedCells = WorksheetFunction.CountA(Range("A1:H" & MaxRow))

If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If

End Sub

Regards

Trevor


"Jeff Green" wrote in message
...
Is there a simple code to search a known range to verify
that each cell has correct data? The catch here is that
I always use columns A thru H, but do not know how many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header), then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.

Tom

OK, that would work too ;-) And it is just a bit shorter than my version.

Regards

Trevor


"Tom Ogilvy" wrote in message
...
That is a good suggestion. It made me think

how about
if application.countA(Range("A:H")) mod 8 < 0 then
msgbox "Missing Data"
End if

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote in message
...
Jeff

one way:

Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long

MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)

ExpectedCells = WorksheetFunction.CountA(Range("A1:H" & MaxRow))

If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If

End Sub

Regards

Trevor


"Jeff Green" wrote in message
...
Is there a simple code to search a known range to verify
that each cell has correct data? The catch here is that
I always use columns A thru H, but do not know how many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header), then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.

I think that is wonderful, can you help me understand
the "mod 8 < 0" part--seems that the 8 is in reference
to the "H", but what makes this work?
-----Original Message-----
That is a good suggestion. It made me think

how about
if application.countA(Range("A:H")) mod 8 < 0 then
msgbox "Missing Data"
End if

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote
in message
...
Jeff

one way:

Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long

MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)

ExpectedCells = WorksheetFunction.CountA(Range("A1:H"
& MaxRow))

If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If

End Sub

Regards

Trevor


"Jeff Green"
wrote in message
...
Is there a simple code to search a known range to
verify
that each cell has correct data? The catch here is
that
I always use columns A thru H, but do not know how
many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header),
then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then
again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.




.

16 mod 8 would return 0. Mode gives you the remainder of dividing 16 by 8
(in the example). since each row should have 8 entries, and countA counts
the cells with entries, one would expect that a square - filled range of 8
columns should have a countA evenly divisible by 8.

--
Regards,
Tom Ogilvy

wrote in message
...
I think that is wonderful, can you help me understand
the "mod 8 < 0" part--seems that the 8 is in reference
to the "H", but what makes this work?
-----Original Message-----
That is a good suggestion. It made me think

how about
if application.countA(Range("A:H")) mod 8 < 0 then
msgbox "Missing Data"
End if

--
Regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote
in message
...
Jeff

one way:

Sub Test()
Dim MaxRow As Long
Dim ExpectedCells As Long

MaxRow = WorksheetFunction.Max( _
Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, _
Range("C65536").End(xlUp).Row, _
Range("D65536").End(xlUp).Row, _
Range("E65536").End(xlUp).Row, _
Range("F65536").End(xlUp).Row, _
Range("G65536").End(xlUp).Row, _
Range("H65536").End(xlUp).Row)

ExpectedCells = WorksheetFunction.CountA(Range("A1:H"
& MaxRow))

If ExpectedCells < MaxRow * 8 Then
MsgBox "Incomplete data"
End If

End Sub

Regards

Trevor


"Jeff Green"
wrote in message
...
Is there a simple code to search a known range to
verify
that each cell has correct data? The catch here is
that
I always use columns A thru H, but do not know how
many
rows it will be each time. So if I have complete
information on rows 2 - 5 (with row 1 as a header),
then
I have only 1 column with data on row 6--it should
trigger a msgbox, etc...

I currently use a Do...Loop Until IsEmpty and then
again
check the entire row, but this is quite slow in my
opinion, surely there could be a faster way.

Thanks if you could help, or let me know if I should
provide more detail.




.