ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   select case issue (https://www.excelbanter.com/excel-programming/413213-select-case-issue.html)

[email protected]
select case issue
 
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[_2_]
select case issue
 
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

Rick Rothstein \(MVP - VB\)[_2187_]
select case issue
 
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


wrote in message
...
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


[email protected]
select case issue
 
On Jun 27, 8:29*am, Stephen Lloyd
wrote:
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- Hide quoted text -

- Show quoted text -

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

[email protected]
select case issue
 
On Jun 27, 8:38*am, wrote:
On Jun 27, 8:29*am, Stephen Lloyd





wrote:
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- Hide quoted text -

- Show quoted text -

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.


All times are GMT +1. The time now is 02:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com