Hi,

Try this: it assumes the rates are in "regular" positions in tyour
workbook C20-C263 C26-C29, etc) - your sample code indicates this is true.


Const BlankRates As String = "You've got some blanks, please complete all
the blanks."


Sub CheckforErrors56()

Dim BlanksFound as boolean, i as integer

BlanksFound = False

For i = 1 To Range("C17")
If Application.CountBlank(Range("C" & (i - 1) * 6 + 20 & " :C" & (i - 1)
* 6 + 23)) = 1 Then
MsgBox BlankRates
BlanksFound = True
Exit For
End If
Next i

If Not BlanksFound Then Call TrimIt1

HTH

End Sub

"Gordon Rainsford" wrote:

How about Select Case?

Gordon Rainsford

Paige wrote:

In my spreadsheet, the user is asked to enter the # of rates in cell C17;
they can enter up to 10. I want to check to see if they've answered all 4
questions for each rate (each set of 4 questions (for each rate) is
non-contiguous with the next set of questions for another rate). A sample
of my current code is below. The problem is that there are up to 10 rates
for 18 different categories. By the time I finish doing all the code, it
will be huge. Surely there is a better way to accomplish this; I'm just an
intermediate VBA skill level (if that), and any help would be appreciated to
do this more efficiently. Thanks for any assistance...


Const BlankRates As String = "You've got some blanks, please complete all
the blanks."

Sub CheckforErrors56()
If Range("C17") = 1 Then
If Application.CountBlank(Range("C20:C23")) = 1 Then
MsgBox BlankRates
Else
Call TrimIt1
End If
Else
If Range("C17") = 2 Then
If Application.CountBlank(Range("C20:C23")) = 1 Or _
Application.CountBlank(Range("C26:C29")) = 1 Then
MsgBox BlankRates
Else
Call TrimIt1
End If
Else
If Range("C17") = 3 Then
If Application.CountBlank(Range("C20:C23")) = 1 Or _
Application.CountBlank(Range("C26:C29")) = 1 Or _
Application.CountBlank(Range("C32:C35")) = 1 Then
MsgBox BlankRates
Else
Call TrimIt1
End If
Else
If Range("C17") = 4 Then
If Application.CountBlank(Range("C20:C23")) = 1 Or _
Application.CountBlank(Range("C26:C29")) = 1 Or _
Application.CountBlank(Range("C32:C35")) = 1 Or _
Application.CountBlank(Range("C38:C41")) = 1 Then
MsgBox BlankRates
Else
Call TrimIt1
End If
Else
Call TrimIt1
End If
End If
End If
End If
End Sub


--
Gordon Rainsford

London UK