Multiple Looping

[svmac]

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>

 

[SIXTH SENSE]

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]

The second one should do the trick.

Thanks very much for your quick assistance SS.

Regards
Stuart

 

[svmac]

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]

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]

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]

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]

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)

 

[SIXTH SENSE]

No probs!