PDA

View Full Version : Loop



Nova
08-26-2004, 08:09 AM
Hi!

I think I need your help again. :bawl
I don't know what I'm doing wrong. It should retrieve the data from the system and print it. And go on to another organization for all the chosen organizations in the ListBox1.

I'm thinking my code for the loop is incorrect.



Dim i As Integer
Dim Chosen As Integer
Dim CurList As ListBox
Dim UserForm As frm

'show the tab
Sheets("NON II").Select

' Initialize the Counter variable.
For i = 0 To ListBox1.ListCount - 1

'pick the right cells to populate


If ListBox1.Selected(i) Then
Worksheets("NON II").Range("F6") = ListBox1.List(i)
Worksheets("NON II").Range("F9") = ComboBox1.Text
Worksheets("NON II").Range("AE9") = ComboBox2.Text


'close the form

frm.Hide
Sheets("NON II").Select
Application.ScreenUpdating = True

:*) (This is where the loop should start)
'Retrieve data for single organization

'RetrieveNON_II Range on NONII tab
Sheets("Non II").Select
esb_Retrieve9

Sheets("NON II").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1




End If

' Increment the Organization Counter so we can get the value of the
' next selected item.

i

' Repeat until all the items in the list have
' been checked.

Next




End Sub

Zack Barresse
08-26-2004, 08:39 AM
Well, for looping through listboxes (as pointed out to me by Andy Pope) is best done backwards. So maybe your syntax should be something like ...

Sub testMe() 'Enter listbox name and process, choose from the declarations drop down
Dim i As Long
On Error Resume Next
For i = ListBox1.ListCount - 1 To 0 Step -1
'what do you want done to your listbox here
Next i
End Sub

This can be any value. It will step through your listbox and perform whatever you want (where it's marked) with that item. Another one going forward might be ...

For i = 1 To ListBox1.ListCount
'what do you want done to your listbox here
Next i


Btw, check out the VBA tags when posting (little green box just above your msg body), it will make your code look like mine does (we thank mark007 for that mod!). :yes

SJ McAbney
08-26-2004, 08:44 AM
Well, for looping through listboxes (as pointed out to me by Andy Pope) is best done backwards.
Why? With what reasoning?

Zack Barresse
08-26-2004, 08:50 AM
Oh, sorry. For deleting multiple listbox entries. I thought I had that in there, my bad. (Sorry 'bout the confustion.)

Nova
09-01-2004, 08:47 AM
Hi!

Sorry, it took me so long to respond... I was out . I think i got my loop going, but ... it performs functions within the loop only twice and then stops... I can't figure out what I did wrong... I have about 52 variables and I want to be able to pick and run more than two at the time... Desperate for help

Private Sub OK_Click()
Dim i As Variant
Dim Chosen As Integer
Dim CurList As ListBox
Dim UserForm As frm

'updated appl
Application.ScreenUpdating = True
'login to the system
MsgBox "Please Sign-on to ""MRUSB"" DATABASE When Promted."
STS = EssVConnect32(Null, gUsername, gPassword, "Ess02.us.bank-dns.com", "MRUSB", "Mrdata")
If STS <> 0 Then Exit Sub
'picking variables
For i = 0 To ListBox1.ListCount - 1

'defining cell location of the comboboxs

If ListBox1.Selected(i) = True Then
Worksheets("NON II").Range("F6") = ListBox1.List(i)
Worksheets("NON II").Range("F9") = ComboBox1.Text
Worksheets("NON II").Range("AE9") = ComboBox2.Text
Worksheets("NON II").Range("f7") = ComboBox4.Text
Worksheets("NON II").Range("f8") = ComboBox5.Text


Sheets("Non II").Select

'Retrieving data from the system

'RetrieveNON_II Range on NONII tab
Sheets("Non II").Select
esb_Retrieve9

'RetrieveExp Range on Exp tab
Sheets("Exp").Select
' esb_Retrieve1


'Print reports

Sheets("NON II").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Sheets("Exp").Select
' ActiveWindow.SelectedSheets.PrintOut Copies:=1



End If

Next i

frm.Hide

Sheets("Non II").Select

End Sub

Zack Barresse
09-01-2004, 09:05 AM
Hey,

Take a look at the VBA tags in the reply & quick reply box, they make your code look like mine above. :)

Maybe you could try changing your For statement from
For i = 0 To ListBox1.ListCount - 1
to
For i = ListBox1.ListCount - 1 To 0 Step - 1

Plus your ranges don't seem to be changing with your loop, which means they will be getting written over for every step through your loop. Is that what you want?

Nova
09-01-2004, 09:27 AM
Thanks!! I forgot about the VBA tags...

I want my ranges to stay the same, the listbox1 is the only one that should change.



Unfortunately your suggestion didn't work.. still runs only twice ;(((((

Zack Barresse
09-01-2004, 09:41 AM
To check your loop, try this ...

Private Sub OK_Click()
Dim i As Integer, n As Integer
Dim Chosen As Integer
Dim CurList As ListBox
Dim UserForm As frm
'updated appl
Application.ScreenUpdating = True
'login to the system
MsgBox "Please Sign-on to ""MRUSB"" DATABASE When Promted."
STS = EssVConnect32(Null, gUsername, gPassword, "Ess02.us.bank-dns.com", "MRUSB", "Mrdata")
If STS <> 0 Then Exit Sub
'picking variables
n = 0
For i = 0 To ListBox1.ListCount - 1
n = n + 1
'defining cell location of the comboboxs
If ListBox1.Selected(i) = True Then
Worksheets("NON II").Range("F6") = ListBox1.List(i)
Worksheets("NON II").Range("F9") = ComboBox1.Text
Worksheets("NON II").Range("AE9") = ComboBox2.Text
Worksheets("NON II").Range("f7") = ComboBox4.Text
Worksheets("NON II").Range("f8") = ComboBox5.Text
Sheets("Non II").Select
'Retrieving data from the system
'RetrieveNON_II Range on NONII tab
Sheets("Non II").Select
esb_Retrieve9
'RetrieveExp Range on Exp tab
Sheets("Exp").Select
' esb_Retrieve1
'Print reports
Sheets("NON II").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Exp").Select
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next i
MsgBox "You looped " & n & " number of loop(s)!", vbOKOnly + vbInformation, "Amount"
frm.Hide
Sheets("Non II").Select
End Sub

It appears your just skipping all the loops if the listbox item isn't selected. Are you sure that's what you want?

Nova
09-01-2004, 12:36 PM
That is exactly what I need
I want to loop only objects that I selected.

Nova
09-01-2004, 02:09 PM
I think I figured out what the problem is, but I don't know how to fix it.
the code executed too quickly for other parts of my

application such as Print comand to keep up. How would I slow it down?

Zack Barresse
09-01-2004, 02:21 PM
I don't think that's the problem. The code will execute one line at a time. Try stepping through your code and/or using break points. Hit Alt + F11 to enter the Visual Basic Editor (VBE). Put your cursor in your code, then press F8 to step through your code 1 line at a time. Keep it at about a half-open screen and you should see what's happening to Excel in the back ground.

What type of ListBox do you have, from the Forms toolbar? If so, is it a multi-select?

Nova
09-02-2004, 05:33 AM
I have done it. It works fine when you step through the code since it slows it down. It won't execute when I press play button. I use the list box from the Forms toolbar and it is a multiple select. It lets me select as many variables as I like... The problem is: every loop it makes for the True chosen a lot of data is retrieved from the system, replaced and then printed.

I need something to slow down the looping process...

Jacob Hilderbrand
09-02-2004, 05:47 AM
To slow down your code try:


Dim MyTimer As Double

MyTimer = Timer
Do
Loop While Timer - MyTimer < 1 'Change as needed, this is one second.

Nova
09-02-2004, 06:58 AM
still did work... I do appreciate your help, guys!

Macro runs fine only when I disable this part of the code
"ActiveWindow.SelectedSheets.PrintOut Copies:=1 ".

SJ McAbney
09-02-2004, 08:12 AM
For deleting multiple listbox entries.
I still don't see the difference going forward or back will make.

Zack Barresse
09-02-2004, 08:38 AM
It's good practice. Like when you loop through a range of cells in a column and check for a condition. If found, row is deleted, that deleted row is shifted up, becoming the row number that you just deleted. Thus skipping that row in your loop as the task was already performed. If you go backwards, bottom to top, you don't run into that problem.

Nova
09-10-2004, 08:55 AM
HI!
I've tried the code it looks perfect, and when I step through it, it works. However as soon as I start running the macro, while the second selected item from the list is chosen and it's printing all the selections in the listbox disappear. It works if only one of the functions within the loop're enabled (retrieve or print), but as soon as I make both enable it doesn't work properly.
I was wondering if you can help me to redesigned the code in the following manner.

1. Array all the choices within the listbox(list all the variables)

2. loop to pick the ones that are chosen or selected from the form

3. store the choices in the macro

4. Run, retrieve and print only for the choices that are selected

If I can, how would I do that or something similar?

Thanks in advance

tinyjack
09-10-2004, 10:30 AM
Quick question: Are you only getting 2 printouts or are you getting multiple printout but with only 2 of the sets of data?

TJ