Checking row for data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CR - 11 Oct 2008 17:15 GMT

I have a worksheet with 16 columns and 54 rows.
Each row has a range D6 to S6, D7 to S7 ect.

I want to run a macro that checks each range to verify that only 6 columns
in each row have data entered.
I guess a dialog box or something to tell me which ranges have <6 or >6 in
them.

Thanks, much appreciated

CR

Reply to this Message

Rick Rothstein - 11 Oct 2008 17:34 GMT

Something like this maybe?

Sub CheckForSixItems()
Dim RW As Long
Dim Items As Long
Dim Answer As String
' Change the 1 to 54 to your actual row range
For RW = 1 To 54
Items = WorksheetFunction.CountA(Range(Cells(RW, "D"), Cells(RW, "S")))
If Items < 6 Then
Answer = Answer & "Row " & RW & " < 6" & vbCrLf
ElseIf Items > 6 Then
Answer = Answer & "Row " & RW & " > 6" & vbCrLf
End If
Next
MsgBox Answer
End Sub

Signature

Rick (MVP - Excel)

>I have a worksheet with 16 columns and 54 rows.
> Each row has a range D6 to S6, D7 to S7 ect.
[quoted text clipped - 7 lines]
>
> CR

Reply to this Message

CR - 11 Oct 2008 17:43 GMT

Outstanding, works like a charm

Thank you very much

CR

> Something like this maybe?
>
[quoted text clipped - 25 lines]
>>
>> CR

Reply to this Message

CR - 11 Oct 2008 18:36 GMT

Is there a way to have the message box give the value of column C in each
range instead of the row number?

Thanks Again

CR

> Outstanding, works like a charm
>
[quoted text clipped - 32 lines]
>>>
>>> CR

Reply to this Message

Rick Rothstein - 11 Oct 2008 19:58 GMT

Here is one possible way...

Sub CheckForSixItems()
Dim RW As Long
Dim Items As Long
Dim Answer As String
' Change the 1 to 54 to your actual row range
For RW = 1 To 54
Items = WorksheetFunction.CountA(Range(Cells(RW, "D"), Cells(RW, "S")))
If Items < 6 Then
Answer = Answer & "Row " & RW & ": " & Cells(RW, "C").Value & _
" (Count < 6)" & vbCrLf
ElseIf Items > 6 Then
Answer = Answer & "Row " & RW & ": " & Cells(RW, "D").Value & _
" (Count > 6)" & vbCrLf
End If
Next
MsgBox Answer
End Sub

Signature

Rick (MVP - Excel)

> Is there a way to have the message box give the value of column C in each
> range instead of the row number?
[quoted text clipped - 39 lines]
>>>>
>>>> CR

Reply to this Message

CR - 11 Oct 2008 20:11 GMT

Thank you, I made that work!
You guys are amazing. I only try to do something like this every couple of
years and would have been frustrated for days trying to do what took you
minutes.

CR

> Here is one possible way...
>
[quoted text clipped - 59 lines]
>>>>>
>>>>> CR

Reply to this Message