'If' Statement - Must be a Better Way to Do This!
Correction
Const BlankRates As String = "You've got some blanks, please complete all
the blanks."
Sub CheckforErrors()
If TestBlanks(Range("C17"), Range("C20:C23"), _
Range("C26:C29"), Range("C32:C35"), _
Range("C38:C41")) = 1 Then
MsgBox BlankRates
Else
Call TrimIt1
End If
Dim tmp
End Sub
Private Function TestBlanks(TestCell As Range, rng1 As Range, rng2 As Range,
_
rng3 As Range, rng4 As Range)
Dim tmp As Long
With Application
Select Case TestCell.Value
Case 1: tmp = tmp + .CountBlank(rng1)
Case 2: tmp = tmp + .CountBlank(rng2)
Case 3: tmp = tmp + .CountBlank(rng3)
Case 4: tmp = tmp + .CountBlank(rng4)
End Select
End With
TestBlanks = tmp
End Function
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Paige" wrote in message
...
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
|