PDA

View Full Version : Solved: Problem with VB CASE statement



siva
12-25-2007, 07:56 AM
Dear Friends,
Wish Merry Christmas to Everyone!

In attached file, when I click the command button,
Columns F,G,H should get the values based on column C.
Column F and G is getting expected result but Column H is not.
The answer of column H should same as column A.
I have problem with Process 3,5,6,7 and 8 only.
The logic is in sheet PIVOT TABLE.

Pls help.


If Cells(1, ActiveCell.Column) <> "Job No." Then
MsgBox "invalid column! you must select column job no."
Else
Rowbase = ActiveCell.Row
columbase = ActiveCell.Column
For RowCount = 1 To Selection.Rows.Count
temp = Rowbase + RowCount - 1
If temp <> 1 Then
System = Mid(Cells(temp, columbase), 6, 3)
Process = Mid(Cells(temp, columbase), 10, 2)
value1 = Cells(temp, columbase)
Select Case True
Case System Like "*" And Process = 1
Cells(temp, columbase + 5) = "Cheng"
Case System Like "*" And Process = 2
Cells(temp, columbase + 5) = "Cheng"
Case System Like "*" And Process = 4 Or Process = 10 Or Process = 14 Or Process = 20
Cells(temp, columbase + 5) = "Lee TS"
Case System Like "*" And Process = 13 Or Process = 18
Cells(temp, columbase + 5) = "Lee WW"
Case System = 209 And Process = 3
Cells(temp, columbase + 5) = "Dong QW"
Case System Like "SOR" And Process = "-9"
Cells(temp, columbase + 5) = "Cheng"
Case System = 205 Or 206 Or 212 Or 214 Or 216 Or 218 Or 222 Or 225 Or 256 Or 260 Or 261 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Mahesh"

Case System = 201 Or 202 Or 203 Or 204 Or 210 Or 219 Or 257 Or 259 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Ganesh"

Case System = 207 Or 220 Or 221 Or 262 Or 263 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Tun Tun"

Case System = 217 Or 224 Or 226 Or 229 Or 230 Or 234 Or 264 Or 265 Or 266 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Yu ZF"

Case System = 200 Or 228 Or 231 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Sub-Con"


Case Else
Cells(temp, columbase + 5) = "Unknown"
End Select

End If
Next RowCount
End If

Norie
12-25-2007, 08:10 AM
Sorry but that logic just isn't, well, logical.

For example as far as I can work out this will deliver one of two values -1 or 511 not True/False.

System = 205 Or 206 Or 212 Or 214 Or 216 Or 218 Or 222 Or 225 Or 256 Or 260 Or 261 _
And Process = 3 Or 5 Or 6 Or 7 Or 8

Aussiebear
12-25-2007, 03:04 PM
In attached file, when I click the command button,
Columns F,G,H should get the values based on column C.
Column F and G is getting expected result but Column H is not.
The answer of column H should same as column A.
I have problem with Process 3,5,6,7 and 8 only.
The logic is in sheet PIVOT TABLE.


Hello Siva, when I click the command button nothing happens...

Besides that if what you are indicating that you are only getting an error with the Process 3,5,6,7 & 8 section, it would seem that maybe the difference between this and the other sections is that you have changed the writing structure of your lines of code.

For example, from this style
Case System Like "*" And Process = 4 Or Process = 10 Or Process = 14 Or Process = 20
Cells(temp, columbase + 5) = "Lee TS"

Case System Like "*" And Process = 13 Or Process = 18
Cells(temp, columbase + 5) = "Lee WW"

to

Case System = 205 Or 206 Or 212 Or 214 Or 216 Or 218 Or 222 Or 225 Or 256 Or 260 Or 261 _
And Process = 3 Or 5 Or 6 Or 7 Or 8
Cells(temp, columbase + 5) = "Mahesh"


From my very limited experience, and after reading other examples on this forum, I would have thought that string values should have been inside of quotation marks such as


Case System = "205" Or "206" Or "212" Or "214" Or "216" Or "218" Or "222" Or "225" Or "256" Or "260" Or "261" _
And Process = "3" Or "5" Or "6" Or "7" Or "8"
Cells(temp, columbase + 5) = "Mahesh"


You've also changed from "Case System Like..." to "Case System =..."

I'm only guessing here, but if your code works for everything other than anything using processes 3,5,6, 7 & 8, well these are the code structure changes that I've noticed.

mikerickson
12-25-2007, 03:22 PM
This line needs to be rewritten, as do the similar lines
Case ((System = 201) Or (System = 202) Or (System = 203) Or (System = 204) Or (System = 210) Or (System = 219) Or (System = 257) Or (System = 259)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))

As Norie was alluding to, the OR's in the original formulation were performing a bit-wise operation on integers.

Aussiebear
12-25-2007, 03:29 PM
Well there you go, I wasn't even close...

siva
12-25-2007, 04:17 PM
Hi Aussiebear,
Clear all value in F2:H483, then press the command button.
The result in H2:H483 should be same as A2:A483.

Aussiebear
12-25-2007, 04:43 PM
Mike, re written as in like this?

If Cells(1, ActiveCell.Column) <> "Job No." Then
MsgBox "invalid column! you must select column job no."
Else
Rowbase = ActiveCell.Row
columbase = ActiveCell.Column
For RowCount = 1 To Selection.Rows.Count
temp = Rowbase + RowCount - 1
If temp <> 1 Then
System = Mid(Cells(temp, columbase), 6, 3)
Process = Mid(Cells(temp, columbase), 10, 2)
value1 = Cells(temp, columbase)
Select Case True
Case ((System = "*")) And ((Process = 1) Or (Process = 2))
Cells(temp, columbase + 5) = "Cheng"

Case ((System = "*")) And ((Process = 4) Or (Process = 10) Or (Process = 14) Or (Process = 20))
Cells(temp, columbase + 5) = "Lee TS"

Case ((System = "*")) And ((Process = 13) Or (Process = 18))
Cells(temp, columbase + 5) = "Lee WW"

Case (System = 209) And (Process = 3)
Cells(temp, columbase + 5) = "Dong QW"

Case (System = SOR) And (Process = -9)
Cells(temp, columbase + 5) = "Cheng"

Case ((System = 205) Or (System = 205) Or (System = 212) Or (System = 214) Or (System = 216) Or (System = 218) _
Or (System = 222) Or (System = 225) Or (System = 256) Or (System = 260) Or (System = 261)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))
Cells(temp, columbase + 5) = "Mahesh"

Case ((System = 201) Or (System = 202) Or (System = 203) Or (System = 204) Or (System = 210) Or (System = 219) _
Or (System = 257) Or (System = 259)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))
Cells(temp, columbase + 5) = "Ganesh"

Case ((System = 207) Or (System = 220) Or (System = 221) Or (System = 262) Or (System = 263)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))
Cells(temp, columbase + 5) = "Tun Tun"

Case ((System = 217) Or (System = 224) Or (System = 226) Or (System = 229) Or (System = 230) Or (System = 234) _
Or (System = 264) Or (System = 265) Or (System = 266)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))
Cells(temp, columbase + 5) = "Yu ZF"

Case ((System = 200) Or (System = 228) Or (System = 231)) _
And ((Process = 3) Or (Process = 5) Or (Process = 6) Or (Process = 7) Or (Process = 8))
Cells(temp, columbase + 5) = "Sub-Con"

Case Else
Cells(temp, columbase + 5) = "Unknown"
End Select

End If
Next RowCount
End If

Aussiebear
12-25-2007, 08:02 PM
Hi Aussiebear,
Clear all value in F2:H483, then press the command button.
The result in H2:H483 should be same as A2:A483.

Cleared data from your suggested range, ran the code and.... it filled Columns F,G & H to the bottom of the sheet.

siva
12-26-2007, 05:17 AM
Hi All,
Problem solved as I rewrite code as given by mikerickson.

Thanks all.
Special thanks to Mike>