"Case" Problem

[Guest]

The trouble with having programmed for a long time in one language is that
your thought system for design constantly works along the same routes. Here
is a simple Case statement...

If dblSubTotal > 0 Then
Select Case strSalaryOrHour
Case "S" And lngPenRow > 0
Set rngWorkCell = Cells(lngPenRow, 20)
Case "S" And lngPenRow = 0
lngPenRow = 1
Set rngWorkCell = Cells(lngPenRow, 20)
Case "H" And lngPn2Row > 0
Set rngWorkCell = Cells(lngPn2Row, 21)
Case "H" And lngPn2Row = 0
lngPn2Row = 1
Set rngWorkCell = Cells(lngPn2Row, 21)
End Select
rngWorkCell.Select
rngWorkCell.Value = dblSubTotal
End If

Where everything is Dimmed, and lngPenRow (or lngPn2Row) will have received
a value in the If construct immediately preceding this. Unfortunately each
Case is based on a compound test, where one part is Character and one part is
Numeric, and (I hate to confess this in public) I did read the Help file on
Case, so I know why that dog don't hunt.

So how must I re-structure this to get to where I'm trying to go
(rngWorkCell.Value = dblSubTotal)?

 

[Guest]

If strSalaryOrHour="S" then
IF lngPenRow >0 then
Set rngWorkCell = Cells(lngPenRow, 20)
ELSE if lngPenRow >0 then
lngPenRow = 1
Set rngWorkCell = Cells(lngPenRow, 20)
END IF
ELSE
put your "H" code here

END IF

 

[Guest]

if strSalaryorHour = "S" then
set rngWorkcells = Cells(lngPenRow - (lngPenrow = 0), _
20)
else
set rngWorkcells = Cells(lngPn2Row - (lngPn2Row = 0), _
20)

End if

Assums lngPenRow and lngPn2Row will never be negative.

 

[Guest]

I knew I was making it too complex! Thanks.

 

[Guest]

You want to use multiple case statements. This will make your code more
efficient as it needs to make fewer comparison. Also when ordering your case
statement make sure to put the most likely situations near the top... So
something like this...

If dblSubTotal > 0 Then
Select Case strSalaryOrHour
Case "S" 'S is more likely than H
Select Case lngPenRow
Case Is > 0 '>0 more likely than 0
Set rngWorkCell = Cells(lngPenRow, 20)
Case 0
lngPenRow = 1
Set rngWorkCell = Cells(lngPenRow, 20)
End Select
Case "H"
Select Case lngPn2Row
Case Is > 0
Set rngWorkCell = Cells(lngPn2Row, 21)
Case 0
lngPn2Row = 1
Set rngWorkCell = Cells(lngPn2Row, 21)
End Select
End Select
rngWorkCell.Select
rngWorkCell.Value = dblSubTotal
End If

 

[George Nicholson]

Select Case True
Case strSalaryOrHour = "S" And lngPenRow > 0
Set rngWorkCell = Cells(lngPenRow, 20)

Case strSalaryOrHour = "S" And lngPenRow = 0
lngPenRow = 1
Set rngWorkCell = Cells(lngPenRow, 20)

Case strSalaryOrHour = "H" And lngPn2Row > 0
Set rngWorkCell = Cells(lngPn2Row, 21)

Case strSalaryOrHour = "H" And lngPn2Row = 0
lngPn2Row = 1
Set rngWorkCell = Cells(lngPn2Row, 21)

Case Else
' ?
End Select

 

[Guest]

Just to add to my email (had to work on something else), if you want to
stick with the case statment:

Public Function Test(a, b)
Test = 0
Select Case True
Case a = 1 And b = 1
Test = 11
Case a = 1 And b = 2
Test = 12
Case a = 2 And b = 1
Test = 21
Case a = 2 And b = 2
Test = 22
End Select
End Function

Represents one way to do that.

 

[Guest]

of course one of the 20's should be 21.

 

[Guest]

Thanks to all for the help on this one. Several different approaches, but I
note that

Select Case True

...is a way to used mixed types within a Case construct.

So much to learn, thank goodness I'm being paid by the hour and not by the
job <g>!

 

[Guest]

Note that Select Case True is not going to be the most efficient method. In
Tom's example lets assume that a = 2. The select case will have to evaluate
both
Case a = 1 And b = 1
and
Case a = 1 And b = 2
before reaching
Case a = 2 And b = 1

Not a big deal in this example since there are only 2 case where a = 1 but
if this is put into a loop the code may be evaluating cases which could not
possible result in true repeatedly (thousands of times).

The general rule for doing case statements efficiently is to do the select
on the most general criteria first to cut down the possible number of cases
and to place the most likely case closest to the top.

For example using Toms example if a = 2 ninety percent of the time then have
your case statement like this...

select case a
Case 2 'always executed
msgbox 2
Case 1 'only executed 10% of the time
msgbox 1
Case Else
msgbox "what the..."
end select

Now lets assume that b = 2 eighty percent of the time. Structure your code
like this...

select case a
Case 2 'Executed 100% of the time
select case b
Case 2 'Executed 90% of the time
msgbox "This a"
Case 1 'Executed rarely
msgbox "That a"
end Select
Case 1 'only executed 10% of the time
select case b
Case 2 'Executed 10% of the time
msgbox "This b"
Case 1 'Almost never executed
msgbox "That b"
end Select
Case Else
msgbox "what the..."
end select

while the code takes a bit more to write it is much more efficient. It
really depends on what you are doing and how much you need to focus on
efficiency...