Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > select case issue
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default 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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Case without Select Case error problem Ayo Excel Discussion (Misc queries) 2 May 16th 08 03:48 PM
Select Case issue Whit Excel Programming 1 April 8th 08 03:37 PM
End Select without Select Case, Block If without End If errors Atreides Excel Programming 12 November 17th 06 05:10 PM
Select Case - issue George Excel Discussion (Misc queries) 7 March 17th 06 05:22 AM


All times are GMT +1. The time now is 08:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement