| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
'If' Statement - Must be a Better Way to Do This!
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
-- 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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) "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
|
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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? 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 -- Gordon Rainsford London UK |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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! "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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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... "Toppers" wrote: 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
'If' Statement - Must be a Better Way to Do This!
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... "Bob Phillips" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
| Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
| Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
| SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Linear Mode
