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
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