Solved: Problem with VB CASE statement

[siva]

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.


[VBA]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[/VBA]

[Norie]

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.
[vba]
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 [/vba]

[Aussiebear]

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
[VBA]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"
[/VBA]
to
[VBA]
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"
[/VBA]

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

[VBA]
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"
[/VBA]

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]

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]

Well there you go, I wasn't even close...

[siva]

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]

Mike, re written as in like this?
[VBA]
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
[/VBA]

[Aussiebear]

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]

Hi All,
Problem solved as I rewrite code as given by mikerickson.

Thanks all.
Special thanks to Mike>