Dynamic Column Array

JPLouw

New Member
Joined
Jul 27, 2011
Messages
14
Hi,

I am trying to code a dynamic column array (don't know if this is the most accurate description of the problem).

I have stock returns data in column B ("B1:B120") on which I am doing analysis. I have nine sets data which is each two columns apart, the next set is in column E ("E1:E120") and so forth. I would like to put it in a For loop to repeat the calculations on each of the nine columns containing data.

How do you specify the Range for the Array so that for each loop it will read the data in a new column? For example, for the initial calculation it uses Range("B1:B120"), for the next run in the loop it uses Range("E1:E120"), and so forth.

Thanks in advance,
Jan
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Example:

Code:
Sub Test()
    Dim i As Long
    Dim Rng As Range
    For i = 2 To 20 Step 3
        Set Rng = Columns(i).Resize(120)
        MsgBox Rng.Address
    Next i
End Sub
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
You can refer to columns using numbers.

so for your example

Code:
for i = 0 to 8
   myCol = i *3 +2
     with cells(myCol,1).resize(120)
       'do whatever needs to be done here
     end with
next i
 

JPLouw

New Member
Joined
Jul 27, 2011
Messages
14
Hi Andrew and Weaver,

Thank you very much for your help, it works great!

Many thanks,
Jan
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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