I used this sequence of IF statements to count the occurences of various
sub-codes of the code "16" in a spreadsheet I did a couple of years ago. It
works fine. But it seems to me I could do the same thing with a Case
statement, I just can't figure out how. Here is the code as is:

For Each rCell In rReason
'Counts Reason Code 16 Contact codes R, A, B & G
If rCell = "16" Then
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "A")
If lCt 0 Then
l16Act = l16Act + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "B")
If lCt 0 Then
l16BGct = l16BGct + 1
Else
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "G")
If lCt 0 Then
l16BGct = l16BGct + 1
lCt = 0
End If

End If
End If

Next rCell

Does anyone see how this could be done using a Case statement?

Thanks in advance.

Is the search letter R,A,B the only thing in the offset cell. Or, always in
the same character?


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"salgud" wrote in message
.. .
I used this sequence of IF statements to count the occurences of various
sub-codes of the code "16" in a spreadsheet I did a couple of years ago.
It
works fine. But it seems to me I could do the same thing with a Case
statement, I just can't figure out how. Here is the code as is:

For Each rCell In rReason
'Counts Reason Code 16 Contact codes R, A, B & G
If rCell = "16" Then
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "A")
If lCt 0 Then
l16Act = l16Act + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "B")
If lCt 0 Then
l16BGct = l16BGct + 1
Else
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "G")
If lCt 0 Then
l16BGct = l16BGct + 1
lCt = 0
End If

End If
End If

Next rCell

Does anyone see how this could be done using a Case statement?

Thanks in advance.

It depends... what kind of text is in the cell you are testing, single
character or multiple character text?

--
Rick (MVP - Excel)


"salgud" wrote in message
.. .
I used this sequence of IF statements to count the occurences of various
sub-codes of the code "16" in a spreadsheet I did a couple of years ago.
It
works fine. But it seems to me I could do the same thing with a Case
statement, I just can't figure out how. Here is the code as is:

For Each rCell In rReason
'Counts Reason Code 16 Contact codes R, A, B & G
If rCell = "16" Then
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "A")
If lCt 0 Then
l16Act = l16Act + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "B")
If lCt 0 Then
l16BGct = l16BGct + 1
Else
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "G")
If lCt 0 Then
l16BGct = l16BGct + 1
lCt = 0
End If

End If
End If

Next rCell

Does anyone see how this could be done using a Case statement?

Thanks in advance.

salgud wrote:
I used this sequence of IF statements to count the occurences of various
sub-codes of the code "16" in a spreadsheet I did a couple of years ago. It
works fine. But it seems to me I could do the same thing with a Case
statement, I just can't figure out how. Here is the code as is:

For Each rCell In rReason
'Counts Reason Code 16 Contact codes R, A, B & G
If rCell = "16" Then
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "A")
If lCt 0 Then
l16Act = l16Act + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "B")
If lCt 0 Then
l16BGct = l16BGct + 1
Else
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "G")
If lCt 0 Then
l16BGct = l16BGct + 1
lCt = 0
End If

End If
End If

Next rCell

Does anyone see how this could be done using a Case statement?

Thanks in advance.

I don't see a compelling reason to use Select/Case. Consider how
Select/Case works. Example:

' test
Sub TestCase()
Dim lCt As Long
lCt = 1
Select Case lCt
Case 1
Debug.Print "1"
lCt = 2
Case 2
Debug.Print "2"
End Select
End Sub
' end test

The test outputs only "1", even though the variable is subsequently set
to 2, because once the first matching case is processed execution will
skip to the end of the Select block. AFAIK VB/A is different in this
respect from other languages that have select/case.

Another reason is the variable you are testing (lCt) is acting like 4
different variables. First it's something about "R", then it's something
about "A", etc.

You /could/ write this as three Select/Case blocks, but since there are
at most two conditions to check (lCt 0, Else) that would be overkill
and If/Then/Else is the conditional of choice.

On Tue, 22 Sep 2009 18:38:36 -0400, Rick Rothstein wrote:

It depends... what kind of text is in the cell you are testing, single
character or multiple character text?

Thanks for your replies. There will be only one character in the cell.

UN tested but
Then, why couldn't it be something simple like
If rCell = "16" Then
x = rCell.Offset(, 2).Value
l16 & "x & " = l16 " & x & "ct + 1
lCt = 0
End If


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"salgud" wrote in message
...
On Tue, 22 Sep 2009 18:38:36 -0400, Rick Rothstein wrote:

It depends... what kind of text is in the cell you are testing, single
character or multiple character text?

Thanks for your replies. There will be only one character in the cell.

This (untested) code should work...

For Each rcell In rReason
Select Case rcell.Offset(0, 2).Value
Case "R", "r"
l16Rct = l16Rct + 1
Case "A", "a"
l16Act = l16Act + 1
Case "B", "b"
l16BGct = l16BGct + 1
Case "G", "g"
l16BGct = l16BGct + 1
End Select
Next

--
Rick (MVP - Excel)


"salgud" wrote in message
...
On Tue, 22 Sep 2009 18:38:36 -0400, Rick Rothstein wrote:

It depends... what kind of text is in the cell you are testing, single
character or multiple character text?

Thanks for your replies. There will be only one character in the cell.

On Tue, 22 Sep 2009 18:03:52 -0500, Don Guillett wrote:

UN tested but
Then, why couldn't it be something simple like
If rCell = "16" Then
x = rCell.Offset(, 2).Value
l16 & "x & " = l16 " & x & "ct + 1
lCt = 0
End If

Boy do I feel dumb. This is brilliant! Thanks, Don.

On Tue, 22 Sep 2009 19:21:22 -0400, Rick Rothstein wrote:

This (untested) code should work...

For Each rcell In rReason
Select Case rcell.Offset(0, 2).Value
Case "R", "r"
l16Rct = l16Rct + 1
Case "A", "a"
l16Act = l16Act + 1
Case "B", "b"
l16BGct = l16BGct + 1
Case "G", "g"
l16BGct = l16BGct + 1
End Select
Next

Thanks, Rick. Exactly what I was asking for.

On Tue, 22 Sep 2009 18:50:16 -0400, smartin wrote:

salgud wrote:
I used this sequence of IF statements to count the occurences of various
sub-codes of the code "16" in a spreadsheet I did a couple of years ago. It
works fine. But it seems to me I could do the same thing with a Case
statement, I just can't figure out how. Here is the code as is:

For Each rCell In rReason
'Counts Reason Code 16 Contact codes R, A, B & G
If rCell = "16" Then
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "R")
If lCt 0 Then
l16Rct = l16Rct + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "A")
If lCt 0 Then
l16Act = l16Act + 1
lCt = 0
End If

lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "B")
If lCt 0 Then
l16BGct = l16BGct + 1
Else
lCt = InStr(1, UCase(rCell.Offset(0, 2).Value), "G")
If lCt 0 Then
l16BGct = l16BGct + 1
lCt = 0
End If

End If
End If

Next rCell

Does anyone see how this could be done using a Case statement?

Thanks in advance.

I don't see a compelling reason to use Select/Case. Consider how
Select/Case works. Example:

' test
Sub TestCase()
Dim lCt As Long
lCt = 1
Select Case lCt
Case 1
Debug.Print "1"
lCt = 2
Case 2
Debug.Print "2"
End Select
End Sub
' end test

The test outputs only "1", even though the variable is subsequently set
to 2, because once the first matching case is processed execution will
skip to the end of the Select block. AFAIK VB/A is different in this
respect from other languages that have select/case.

Another reason is the variable you are testing (lCt) is acting like 4
different variables. First it's something about "R", then it's something
about "A", etc.

You /could/ write this as three Select/Case blocks, but since there are
at most two conditions to check (lCt 0, Else) that would be overkill
and If/Then/Else is the conditional of choice.

Thanks for your reply. I'm not sure I follow your logic, though I agree
with your conclusion (see Don's post above).

lCt is merely a test, like a Boolean, for whether or not the cell contains
one of the sought after codes (A, B, G, R). So the code can output one of 4
variables, l16Rct, l16Bct, l16Gct or l16Act. It may be my variable naming
schema that you find confusing. (I think if I were doing it now, I would
use something that would put the A, B, G or R at the end of the variable
name to make it clearer. That would also make Don's code a little simpler.)

Don's code very cleverly uses the cell contents to name the variables and
reduces this to a few lines of relatively simple code, just as you
suggested. So, in the end, you're right, this doesn't call for a Select
Case statement at all.