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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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
 

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182
The second one should do the trick.

Thanks very much for your quick assistance SS.

Regards
Stuart
 

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182
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
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883

ADVERTISEMENT

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
 

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182
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
 

svmac

Board Regular
Joined
Feb 16, 2002
Messages
182

ADVERTISEMENT

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
 

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

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
Top