'If' Statement - Must be a Better Way to Do This!

G

Guest

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
 
G

Gordon Rainsford

How about Select Case?

Gordon Rainsford

Paige said:
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
Click to expand...
 
B

Bob Phillips

Here is a technique that should make it easier to maintain

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

Sub CheckforErrors56()
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 Sub TestBlanks(TestCell As Range, rng1 As Range, rng2 As Range, _
rng3 As Range, rng4 As Range)
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 Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

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)
 
G

Guest

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
 
G

Guest

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?
 
G

Guest

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
 
G

Guest

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?
 
G

Gordon Rainsford

Paige said:
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?
Click to expand...

How about:

Dim i As Integer
If Range("c17").Value < 10 Then
i = Range("c17").Value
Else
i = 10
End If

Select Case i
' and then your various Cases below
 
G

Guest

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
 
G

Guest

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....
 
G

Guest

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
 
G

Guest

Thanks Gordon for responding. I'm trying all the variations suggested and
they all work, each in a different way of course, and I'm learning alot from
all the suggestions. Appreciate your assistance - have a great day!
 
G

Guest

Great! Thanks again for all your help ...your assistance is very much
appreciated; maybe in 10 years this will be simple, but right now, being able
to get help is just invaluable. Have a wonderful day...
 
G

Guest

Just wanted to say thanks, Bob, for your taking the time to respond and
assist; I'm trying all the variations suggested; this has really been
helpful. My job is developing Excel spreadsheets, so every little tidbit of
information/assistance is useful (and educational), and very much
appreciated; have a good day...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Shorting code 3
VBA Code... 7
How to hide password? 1
[VBA] SUM RANGE OF VALUES 3
There must be a better way to code this 7
there must be a more simple way of doing this? 2
AutoDetect the path of the file? 4
Some trouble assigning a string to a variable 22

Top