VBA Cell Referencing Help

[Felix_Dragonhammer]

So I have problem with referencing cells in VBA to make my code more dynamic.

I have a cell, B3, that contains a number (currently 23) that represents the current week. Everytime I run my code, the B3 gets one added to it.

I have a section of code I'd like to modify to reflect the change in week:

Range("AC11,AC11,AC17,AC23,AC29,AC35,AC41,AC47,AC53,AC59,AC65").Select

I'd like the cells to reference B3 so that everytime B3 updates, the column increases by 1. Also, the column number is always 5 more than the value in B3. So basically, when I run the code, I want the Range to hit all the rows mentioned in the code above in Column 29, and next week I want it to select all the rows in Column 30.

Thank you!

 

[Felix_Dragonhammer]

If it's simpler, I can just create a formula in another cell that will have the column number I need to reference. However, I still need to know how to reference the cell so that the value is inputted in the code.

 

[kdave]

ColToRef=range("B3").Value
ColToRef=ColToRef + 5
For n = 11 to 65 Step 6
     cells(n, ColToRef).Select
     'Do what you want with the selected cell
Next n

Not tested.

 

[Felix_Dragonhammer]

Thank you for that bit of code. I just have a question: to format the cells I want, would I type them where the 'n' is in the 4th row? Like this?

ColToRef=range("B3").Value
ColToRef=ColToRef + 5
For n = 11 to 65 Step 6
     cells(n("AC11,AC11,AC17,AC23,AC29,AC35,AC41,AC47,AC53,AC59,AC65", ColToRef).Select
     'Do what you want with the selected cell
Next n

 

[Felix_Dragonhammer]

Never mind, I see what you did there with the "For n=11 to 65 Step 6" line. I am new to VBA and still learning, so thanks for the help!