Try changing the select case to the following

Select Case cl.Value
Case 35, 44, 45, 46
myVal = 1
Case 37, 38, 39, 54, 55
myVal = 3
Case 40, 41, 42, 43
myVal = 5
Case 47, 48, 49, 146 To 159, 201 To 210
myVal = 6
'etc
Case Else:
End Select


" wrote:

here's what i have and im working with it it didnt work for some
reason.
as u can see its workin off a command button. if anyone can help it
would be much appreciated

Private Sub CommandButton1_Click()
Dim a, g As Integer
Dim strBlah As String

Application.Calculation = xlCalculationManual
Sheet2.Range("A9:AB20000").Select
Selection.WrapText = True
g = 9 'starting row

For a = 2 To 20000
If Sheet3.Cells(a, 14) = Sheet2.Cells(2, 2) Then
'If Sheet1.Cells(a, 34).Value = Sheet2.cmbVTYPE.Text Then
Sheet2.Cells(g, 1) = "CWS"
Sheet2.Cells(g, 2) = "CWS-BG-" & Sheet3.Cells(a, 3)
Sheet2.Cells(g, 3) = "TRUE"
Sheet2.Cells(g, 4) = "FALSE"
Sheet2.Cells(g, 5) = "FALSE"
Sheet2.Cells(g, 6) = "FALSE"
Sheet2.Cells(g, 7) = "FALSE"
Sheet2.Cells(g, 8) = Sheet3.Cells(a, 3)
Sheet2.Cells(g, 11) = Sheet3.Cells(a, 18)
Sheet2.Cells(g, 12) = Sheet3.Cells(a, 1)
'Sheet2.Cells(g, 16) = Sheet3.Cells(a, 11)
strBlah = Sheet3.Cells(a, 6)
Sheet2.Cells(g, 24) = strBlah
g = g + 1
End If
Next a

Dim cl As Range
Dim myVal As Variant
For Each cl In Range("$Q$9:$Q$" & Range("$Q$65536").End(xlUp).Row)
Select Case cl
Case Is = 35, 44, 45, 46: myVal = 1
Case Is = 37, 38, 39, 54, 55: myVal = 3
Case Is = 40, 41, 42, 43: myVal = 5
Case Is = 47, 48, 49, 146 To 159, 201 To 210: myVal = 6
'etc
Case Else:
End Select
Cells(cl.Row, "Y") = myVal
Next cl
End Sub

Application.Calculation = xlCalculationAutomatic
End Sub