| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Case" Problem
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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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 "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
I knew I was making it too complex! Thanks.
-- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
of course one of the 20's should be 21.
-- Regards, Tom Ogilvy "Dave Birley" wrote: I knew I was making it too complex! Thanks. -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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 -- HTH... Jim Thomlinson "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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 "Dave Birley" wrote in message ... 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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! -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Case" Problem
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... -- HTH... Jim Thomlinson "Dave Birley" wrote: 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! -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Dave Birley" wrote: 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)? -- Dave Temping with Staffmark in Rock Hill, SC |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lower case letter "i" always converts to upper case | Excel Discussion (Misc queries) | |||
| Converting "uppercase" string data to "lower case" in CSV file | Excel Discussion (Misc queries) | |||
| how do I count only lower case "x" and exclude upper case "X" | Excel Worksheet Functions | |||
| Problem: Worksheets("New Style 2006").Unprotect Password:="naPrint" | Excel Programming | |||
Hybrid Mode
