I wasn't sure if you had the code working ..so here is the complete code again.

Sub CheckforErrors56()

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


BlanksFound = False

If Range("C17") = "11+" Then ' Treat as 10
n = 10
Else
n = CInt(Range("C17")) ' Values 1 to 10
End If

For i = 1 To n
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

End Sub

"Paige" wrote:

Unfortunately, 11+ is used in multiple other places in my spreadsheet,
including in many formulas, so that's why I needed it to remain showing as
11+ in C17, and was trying to adjust the code so it could see 11+ the same as
10. In my simple mind, I tried using variations of the Val< , but that
didn't work...at least I couldn't make it work. Sorry for this being so
complicated....

"Toppers" wrote:

With my last code, C17 will still show "11+" but the loop which tests for
blanks will set as if 10 had been entered (the IF ... THEN test to set the
value of the variable "n").

If the testing for blanks for 11+ is the same as 10, why have it (11+) at
all? ... unless it is used elsewhere in your code.

HTH

"Paige" wrote:

Sorry about that - yes I was referring to your code. My problem is, I
already have code for data validation for that cell (which produces a
drop-down where they can select 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11+). If they
select 11+, I need the cell to keep showing that value, but the testing for
blanks in this case to assume the same as if they had entered 10. Does that
make sense?


"Toppers" wrote:

Paige,
Not sure if you were referring to my code (rather than Gordon)
but the following should work ( I tried it using a Data Validation drop-down)


If Range("C17") = "11+" Then
n = 10
Else
n = CInt(Range("c17"))
End If

For i = 1 To n

HTH

"Paige" wrote:

Thanks everyone! Am in the process of trying these out now. One question,
for Gordon. The user has the option to select (via a drop-down) in C17 the
following: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11+. Your code works great, but
I've been trying to adjust it to accept 11+ as 10. Is there a way to add
that if C17 =11+, then treat it the same as C17 = 10?


"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