Arrays and "For" "Next" loops

S

Spyntek

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("C98:D98"))
Sheet3.Range("C91").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E98:F98"))

Case "Bob"
Sheet1.Range("A98").Value = "29 120"
Sheet3.Range("D92").Value = "Bob"
Sheet3.Range("B92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("C98:D98"))
Sheet3.Range("C92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E98: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
 
M

merjet

Something like this may be what you want.

Private Sub CommandButton3_Click()
'Enter something in D90 so D91 is used at 1st click
Dim iEnd As Integer
iEnd = 1 + Sheets(3).Range("D65536").End(xlUp).Row
Sheets(1).Range("A98").Value = "29 120"
Sheets(3).Range("D" & iEnd).Value = ComboBox1.Value
Sheets(3).Range("B" & iEnd).Value =
Application.WorksheetFunction.Sum(Sheets(1).Range("C98:D98"))
Sheets(3).Range("C" & iEnd).Value =
Application.WorksheetFunction.Sum(Sheets(1).Range("E98:F98"))
End Sub

However, I can only guess since your post is far from clear. Your 2nd
Sub seems to assume Joe and Bob could be a ComboBox1.Value. However,
the 1st Sub puts them in ComboBox4. Your post addresses handling user
input from only 1 of the 4 ComboBoxes. You don't need a CommandButton
to fill the ComboBoxes. You can do that in UserForm_Activate(). You
don't need 4 different loop variables to fill the ComboBoxes; 1 will
do. As my code implies, your references to sheets won't work. They
need to be, e.g., Sheets(3) or Sheets("Sheets3").

Hth,
Merjet
 
M

matt

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("C98:D98"))
Sheet3.Range("C91").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E98:F98"))

Case "Bob"
Sheet1.Range("A98").Value = "29 120"
Sheet3.Range("D92").Value = "Bob"
Sheet3.Range("B92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("C98:D98"))
Sheet3.Range("C92").Value =
Application.WorksheetFunction.Sum(Sheet1.Range("E98: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
Click to expand...

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
 
S

Spyntek

Something like this may be what you want.

Private Sub CommandButton3_Click()
'Enter something in D90 so D91 is used at 1st click
Dim iEnd As Integer
iEnd = 1 + Sheets(3).Range("D65536").End(xlUp).Row
Sheets(1).Range("A98").Value = "29 120"
Sheets(3).Range("D" & iEnd).Value = ComboBox1.Value
Sheets(3).Range("B" & iEnd).Value =
Application.WorksheetFunction.Sum(Sheets(1).Range("C98:D98"))
Sheets(3).Range("C" & iEnd).Value =
Application.WorksheetFunction.Sum(Sheets(1).Range("E98:F98"))
End Sub

However, I can only guess since your post is far from clear. Your 2nd
Sub seems to assume Joe and Bob could be a ComboBox1.Value. However,
the 1st Sub puts them in ComboBox4. Your post addresses handling user
input from only 1 of the 4 ComboBoxes. You don't need a CommandButton
to fill the ComboBoxes. You can do that in UserForm_Activate(). You
don't need 4 different loop variables to fill the ComboBoxes; 1 will
do. As my code implies, your references to sheets won't work. They
need to be, e.g., Sheets(3) or Sheets("Sheets3").

Hth,
Merjet
Click to expand...

I am sorry as to the vagueness of the post. Let me try to clarify. I
have 4 comboBoxes that contain different names. The names are to be
selected depending on if the jobs they are to do happen daily, weekly,
monthy or annually, so I needed 4 comboboxes set up accordingly. That
I am not concerned with. I am sorry for the combobox labels as that
is the way they came numbered and will be changed.

Now depending on which combobox is used and which name is selected in
that combobox will determine where their names appear in column D on
sheet3.

I used the arrays so that their index numbers could be used later in a
FOR NEXT loop, if that is the easiest and most appropriate way to
perform the function I want.

What I was hoping to perfom is this, to some extent

If say Joe's name was selected, which is now in the array at position
index number 0
I want to take Joe's name from the combobox and place it on sheet 3 on
column D starting at row 91. But let's say Kelly's name was selected,
her name is at position 6 in the array. I would want to place it in
colum D, row 97, and so on and so forth.

Now originally, I had thought that using case statements would to the
trick, but that is not the most efficient way to perform this task and
was thinking of a FOR NEXT loop using the index numbers in the array
to perfom that function.

I hope that explains it better
 
S

Spyntek

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

- Show quoted text -
Click to expand...

Matt, that really helps and it seems that is exaclty what I was
looking for. I appreciate your help. Thank you
 
T

Tom Ogilvy

As my code implies, your references to sheets won't work. They
need to be, e.g., Sheets(3) or Sheets("Sheets3").

Sheet1.Name = "House"
? sheet1.Name
House

doesn't seem to cause a problem for me. Could you clarify?
 
T

Tom Ogilvy

maybe something like this assuming the code is in a userform module or in a
worksheet module:

' at the top of the module:
Dim Jobs(22) As String

Private Sub CommandButton1_Click()\

Dim I as long, J as long
Dim K as Long, 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


Private Sub CommandButton3_Click()
Dim i as Long, j as Long
dim rng1 as Range, rng2 as Range
Dim rng3 as Range, rng3a as Range
Dim rng4 as Range, rng4a as Range

set rng1 = sheet3.Range("D91")
set rng2 = Sheet1.Range("A98") ' "29 120"
set rng3 = Sheet3.Range("B91")
set rng3a = Sheet1.Range("C98:D98")
set rng4 = Sheet3.Range("C91")
set rng4a = Sheet1.Range("E98:F98")
for j = 1 to 4
set cbox = me.Controls("Combobox" & j)
for i = lbound(Jobs) to Ubound(Jobs)
if jobs(i) = cbox.Value then
rng1.offset(i,0) = jobs(i)
rng2.Value = "29 120"
rng3.offset(i,0).Value = Application.Sum(rng3a)
rng4.offset(i,0).Value = Application.Sum(rng4a)
end if
Next i, j
end sub
 
S

Spyntek

maybe something like this assuming the code is in a userform module or in a
worksheet module:

' at the top of the module:
Dim Jobs(22) As String

Private Sub CommandButton1_Click()\

Dim I as long, J as long
Dim K as Long, 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

Private Sub CommandButton3_Click()
Dim i as Long, j as Long
dim rng1 as Range, rng2 as Range
Dim rng3 as Range, rng3a as Range
Dim rng4 as Range, rng4a as Range

set rng1 = sheet3.Range("D91")
set rng2 = Sheet1.Range("A98") ' "29 120"
set rng3 = Sheet3.Range("B91")
set rng3a = Sheet1.Range("C98:D98")
set rng4 = Sheet3.Range("C91")
set rng4a = Sheet1.Range("E98:F98")
for j = 1 to 4
set cbox = me.Controls("Combobox" & j)
for i = lbound(Jobs) to Ubound(Jobs)
if jobs(i) = cbox.Value then
rng1.offset(i,0) = jobs(i)
rng2.Value = "29 120"
rng3.offset(i,0).Value = Application.Sum(rng3a)
rng4.offset(i,0).Value = Application.Sum(rng4a)
end if
Next i, j
end sub

--
Regards,
Tom Ogilvy




















- Show quoted text -
Click to expand...

In regards to the line "Set cbox = Me.Controls("Combobox" & j)",
unforutnately and for some unknown reason I get a "compile error"
"Method or data member not found". Upon further review, I noticed
that Controls collection is not available. Any reason why it would
not be available????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile error 10
slow for loops ... better way using arrays or something? 4
list box remove item by combobox 4
Endless looping with Worksheet_change event (XL2003) 3
Inaccurate count with code 6
VBA copy 'corrupts' font selection (XL2003) 2
Re-asking about "ugly" procedure ?? 3
Loops 1

Top