|
For...Next or Select Case Statement
I am having difficulty determining which decision statement is the best for
my situation. What the code is supposed to do, is loop through the multiple choices made by a user in a userform listbox and perform one or more of the listed statements depending on the choice. Below is my attempt at using the Select Case statement, but I don't think Select Case will allow more than one case to be true. As you can see, I am in desperate need of your help. Thanks in advance! For i = 0 To Change_Region.ListBox1.ListCount - 1 If Change_Region.ListBox1.Selected(i) = True Then Change_Region.Hide Application.ScreenUpdating = False Select Case ListBox1.Selected(i) Case Is = 0 Application.Goto ("Africa") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 1 Application.Goto ("Central_Europe") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 2 Application.Goto ("Fmr_Soviet_Union") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 3 Application.Goto ("Indian_Subcontinent") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select End Select End If Next i |
For...Next or Select Case Statement
Your code tests ListBox1.Selected(i) in the Select Case statement.
Selected(i) will return only True of False, no other value. You have already tested in your For i statement the Selected(i) property, so you certainly don't want to test it again in the Select Case. Instead, if I follow your code properly, you should test only the value of i itself. E.g., Select Case i Case .... End Select Each Case clause will perform action to specified Worksheets(i). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "VBA_Newbie79" wrote in message ... I am having difficulty determining which decision statement is the best for my situation. What the code is supposed to do, is loop through the multiple choices made by a user in a userform listbox and perform one or more of the listed statements depending on the choice. Below is my attempt at using the Select Case statement, but I don't think Select Case will allow more than one case to be true. As you can see, I am in desperate need of your help. Thanks in advance! For i = 0 To Change_Region.ListBox1.ListCount - 1 If Change_Region.ListBox1.Selected(i) = True Then Change_Region.Hide Application.ScreenUpdating = False Select Case ListBox1.Selected(i) Case Is = 0 Application.Goto ("Africa") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 1 Application.Goto ("Central_Europe") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 2 Application.Goto ("Fmr_Soviet_Union") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 3 Application.Goto ("Indian_Subcontinent") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select End Select End If Next i |
For...Next or Select Case Statement
On Jul 2, 2:56 pm, VBA_Newbie79
wrote: I am having difficulty determining which decision statement is the best for my situation. What the code is supposed to do, is loop through the multiple choices made by a user in a userform listbox and perform one or more of the listed statements depending on the choice. Below is my attempt at using the Select Case statement, but I don't think Select Case will allow more than one case to be true. As you can see, I am in desperate need of your help. Thanks in advance! For i = 0 To Change_Region.ListBox1.ListCount - 1 If Change_Region.ListBox1.Selected(i) = True Then Change_Region.Hide Application.ScreenUpdating = False Select Case ListBox1.Selected(i) Case Is = 0 Application.Goto ("Africa") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 1 Application.Goto ("Central_Europe") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 2 Application.Goto ("Fmr_Soviet_Union") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select Case Is = 3 Application.Goto ("Indian_Subcontinent") Selection.Copy Worksheets("CAPSDATA").Activate Worksheets("CAPSDATA").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select ActiveSheet.Paste Worksheets("CAPSDATA").Range("A1").Select End Select End If Next i Select Case will use the code for the first true condition found. But it will do so for each iteration of the For-Next loop. If the loop runs four times, the Select Case will run each time. Mark Lincoln |
For...Next or Select Case Statement
Thank you, both. This clears up my confusion!
|
| All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com