Dynamic Column Array

[JPLouw]

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

 

[Andrew Poulsom]

Example:

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]

You can refer to columns using numbers.

so for your example

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]

Hi Andrew and Weaver,

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

Many thanks,
Jan