VBA Loop

GRCArizona

Board Regular
Joined
Apr 24, 2010
Messages
92
Hi - I've got a worksheet that summarizes data from several other Client workbooks. I've written VBA code that opens up the first Client workbook and pulls all the info that I'm looking for. What I'm looking for is something that at the end of my current code (for Client 1) will loop back to the beginning of the code and repeat the code for the remaining Client workbooks.

What I have is a range of cells (A11:A15) with 1,2,3,4,5 which represents the number of Client workbooks. I have the following code at the beginning of my macro:

Dim sClient_Selection As String
sClient_Selection = Range("Client1").Value

What I'm looking for is something where I can change the "1" in Range("Client1").Value to "2", then re-run the code then go back to the beginning of the code and change it to "3", etc.

It would be easy for me to copy the code and simply change the "1" to "2", etc, but I have A LOT of code for "1" and the macro code will get too lengthy very quickly.

Isn't there sth like a For each i, or Loop Until?

Any help would be appreciated.
thx,
GRC
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Here are a couple of ways you might try...

Code:
Dim i as Integer
For i = 1 to 5
sClient_Selection = Range("Client" & i).Value
Next i

Code:
Dim Client As Variant
For Each Client In Array("Client1", "Client2", "Client3", "Client4", "Client5")
sClient_Selection = Range(Client).Value
Next Client
 

GRCArizona

Board Regular
Joined
Apr 24, 2010
Messages
92
That's it! Perfect, thx a bunch. I knew it was sth like that. I was putting extra quotes around the "i".

GRC
 

GRCArizona

Board Regular
Joined
Apr 24, 2010
Messages
92
Actually, the code is erroring out when it trys to start bringing over the info for the 3rd client. Any thoughts...?

Dim i As Integer
For i = 1 To 5

Dim sClient_Selection As String
sClient_Selection = Range("Client" & i).Value

Workbooks.Open Filename:= _
"" & sClient_Selection & ""
 

Watch MrExcel Video

Forum statistics

Threads
1,123,122
Messages
5,599,826
Members
414,341
Latest member
Mohammedsobhey

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