Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and "For" "Next" loops
Hello. I am new to this group so please provide some constructive
criticism if I make a mistake as I will be happy to correct any that I might make. I have created some combo boxes using arrays as shown below Private Sub CommandButton1_Click() Current_Date Dim Jobs(22) As String Dim I, J, K, L As Integer Jobs(0) = "Joe" Jobs(1) = "Bob" Jobs(2) = "Steve" Jobs(3) = "Ray" Jobs(4) = "Tim" Jobs(5) = "Jim" Jobs(6) = "Kelly" Jobs(7) = "Rich" Jobs(8) = "Tom" Jobs(9) = "David" Jobs(10) = "John" Jobs(11) = "Chuck" Jobs(12) = "Tracy" Jobs(13) = "Michelle" Jobs(14) = "Kathy" Jobs(15) = "Chris" Jobs(16) = "Andrea" Jobs(17) = "Jason" Jobs(18) = "Yvette" Jobs(19) = "ALex" Jobs(20) = "Tricia" For I = 0 To 6 ComboBox4.AddItem (Jobs(I)) Next I For J = 7 To 12 ComboBox1.AddItem (Jobs(J)) Next J For K = 13 To 18 ComboBox2.AddItem (Jobs(K)) Next K For L = 19 To 20 ComboBox3.AddItem (Jobs(L)) Next L End Sub With these arrays, if the person using this form selects a certain name, I want to be able to place that name in a certain cell on another sheet of this workbook starting from cell "D91" and place any other names in cells that, when using their index number in the array, add that number to cell "D91" to correctly place any other name. I started doing so using case statements as shown below Private Sub CommandButton3_Click() Select Case ComboBox1.Value Case "Joe" Sheet1.Range("A98").Value = "29 120" Sheet3.Range("D91").Value = "Joe" Sheet3.Range("B91").Value = Application.WorksheetFunction.Sum(Sheet1.Range("C9 8:D98")) Sheet3.Range("C91").Value = Application.WorksheetFunction.Sum(Sheet1.Range("E9 8:F98")) Case "Bob" Sheet1.Range("A98").Value = "29 120" Sheet3.Range("D92").Value = "Bob" Sheet3.Range("B92").Value = Application.WorksheetFunction.Sum(Sheet1.Range("C9 8:D98")) Sheet3.Range("C92").Value = Application.WorksheetFunction.Sum(Sheet1.Range("E9 8:F98")) End Select But I wish not to do that with all 20 names. I want to use a for next loop to replace all the case statements as it would make for more lean programming and a lot less work for me. Does anyone have any suggestions?? The help would be greatly appreciated |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |