On Mar 27, 6:46 am, wrote:
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

It seems as if your "Range("D91")" corresponds with Jobs(0);
"Range("D92")" corresponds with Jobs(1); and so on. If this is the
case then you could use an offset property based on the selection to
output the desired value. In this case, you can use
Range("D91").Offset(0,a).Select; where a is the "bookmark" in the
For...Next loop (For a = 0 To x).

You could do a loop where the selected item from the combo box is
compared to the array and the program does an Exit For when the item
equals the corresponding value in the array. This way you'll have the
element number of the array that corresponds with the selected item in
the combo box.

An additional comment is that you could search VBE for "ListIndex
Property" and lookup what this property does. Basically, it will give
you the index number of the item selected in the combo box.

I hope that these ideas are somewhat helpful.

Matt