Multiple Looping

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182
I am trying to have 2 loops within the same procedure. The procedure is attempting to loop through a list of area servers (firstly) and the a list of local servers after that. The problem I have is discerning between them. I usually use:

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(SvrName)
</FONT>

However I cannot work out how to have 2 separate loops in the same procedure. I have attached the start of my code below. I get an error message:

Run Time Error 1004
"Method 'Range' of object '_Global' failed

All assistance appreciated.

Thnx
Stuart


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetAllData()
<SPAN style="color:#00007F">Dim</SPAN> BrName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ThisMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> SvrName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
Sheets("DataEntry").Select

Load DateSelector
DateSelector.Show

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Range(ListSvrs)

SvrName = Cell.Value

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(SvrName)</FONT>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
hi!
it should be like this.

For Each listsvrsCell In Range(ListSvrs)

SvrName = Cell.Value

next listsvrsCell

For Each SvrNamecell In Range(SvrName)
'your code here
next SvrNamecell
but if you mean to run the other loop each iteration of the first loop, then it should be like this.


For Each listsvrsCell In Range(ListSvrs)

SvrName = Cell.Value

For Each SvrNamecell In Range(SvrName)
'your code here

next SvrNamecell


next listsvrsCell
 
Upvote 0
The second one should do the trick.

Thanks very much for your quick assistance SS.

Regards
Stuart
 
Upvote 0
Re: Multiple Looping, More help please!

I have had a chance to apply the code supplied by Sixth Sense, however am still having some troubles with it.

I still get the same error message:
Run Time Error 1004
"Method 'Range' of object '_Global' failed
I am not sure where to go next, any assistance is appreciated. The code I have used is below:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> GetAllData()
<SPAN style="color:#00007F">Dim</SPAN> BrName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ThisMonth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
Sheets("DataEntry").Select
Load DateSelector
DateSelector.Show
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ListSvrsCell <SPAN style="color:#00007F">In</SPAN> Range(ListSvrs)
SvrName = ListSvrsCell.Value
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> SvrNameCell <SPAN style="color:#00007F">In</SPAN> Range(SvrName)
BrName = SvrNameCell.Value
ThisMonth = Range("B100").Value
Workbooks.Open FileName:= _
"\\" & SvrName & "\BranchG\" & BrName & "\Loans Monitoring\Loan Register.xls"
<SPAN style="color:#007F00">'The code continues from here, doing various things that actually work!</SPAN>
Windows("Loan Register.xls").Activate
ActiveWindow.Close
Windows("Weekly Report.xls").Activate
<SPAN style="color:#00007F">Next</SPAN> SvrNameCell
<SPAN style="color:#00007F">Next</SPAN> ListSvrsCell
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Any assistance is greatly appreciated.

Thanks again
Stuart
 
Upvote 0
hi!
what line does the code stops?
I suspect that this line is in error if "listSvrs" is a range name and not a variable containing the range name.
For Each ListSvrsCell In Range(ListSvrs)
This should be
For Each ListSvrsCell In Range("ListSvrs")
if it is a range name
 
Upvote 0
Yes, that was it. I now get the same message on the line:

For Each SvrNameCell in Range("SvrName")

I added the "" around the range name here, but the code stops here. I think it may be a problem having a variable and a range with the same name, however I need the value from the first 'For Each' to carry into the second. Is there a way to do this?

To explain a little more, there are 3 servers, each with 5-10 servers sitting within it. I need to loop through the first 3, while 'stopping' and performing an action at the 5-10 within it.

Hope that explains my aim, rather than confusing it more!

Cheers
Stuart
 
Upvote 0
Fear not! My stupidity knows no bounds today!

I forgot that SvrName is a variable, not a range. I now have it working.

Thanks Sixth Sense for your assistance!

Stuart
 
Upvote 0
hi!
this line should not have qoutes because this is a variable containing the Svrname
For Each SvrNameCell in Range("SvrName")
unlike the other one whi is a constant.
this should be
For Each SvrNameCell in Range(SvrName)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top