Can this be done as a Case statement?

[salgud]

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.

 

[Don Guillett]

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

 

[Rick Rothstein]

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

 

[smartin]

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.

 

[salgud]

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.

 

[Don Guillett]

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

 

[Rick Rothstein]

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

 

[salgud]

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.

 

[salgud]

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.

 

[salgud]

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.