select case issue

  • Thread starter Thread starter jzamilpa3
  • Start date Start date
J

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
 
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
 
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
 
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 -
Click to expand...

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

- Show quoted text -
Click to expand...

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.
 
Back
Top