select case issue

[jzamilpa3]

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

 

[Stephen Lloyd]

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

 

[Rick Rothstein \(MVP - VB\)]

Including the code was good; however, "it didn't work for some reason"
doesn't tell us a whole lot. Can you describe what you expected to happen
when you ran your code along with what you actually saw happen?

Rick

 

[jzamilpa3]

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










- Show quoted text -

im still having trouble. nothing output's to the end select column

 

[jzamilpa3]

im still having trouble. nothing output's to the end select column- Hide quoted text -

- Show quoted text -

i want it to look at, column Q starting row 9, in column Q there will
be numbers in between letters like "RR35 LEFT" and if is checked to be
between the select case. 35 should = 1. right now it doesnt do much,
when i tried in a diffrent column it deleted wording on row 8 and 9
but thats about it.