1

So I need to select certain columns starting with Column 11 all the way to 1000. I need to apply a formula along each selected column. The columns I need to select are in 4 steps. The code I used is

For colNum = 11 To 1000 Step 4
            Range(Cells(2, colNum), Cells(225, colNum)).FormulaR1C1 = "=(D2)(G2)*RC[-2]"
Next colNum

The code garbs the correct value for RC[-2] but when it goes to the next row, the cells D2 and G2 in the formula don't change to D3 and G3 / to the corresponding cells in the next row. Any ideas?

Thanks in advance!

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3720702
  • 193
  • 2
  • 4
  • 12

1 Answers1

0

Try this:

For colNum = 11 To 1000 Step 4
    Range(Cells(2, colNum), Cells(225, colNum)).FormulaR1C1 = "=RC4*RC7*RC[-2]"
Next colNum

If D and G is fixed and only the last cell is moving.
I'm not sure what you want to accomplish with (D2)(G2), it throws #REF! error.
Also, don't mix it up. If you want to use R1C1, then be consistent.
Below is the non-R1C1 formula of above.

Edit1: Based on comment

Dim i As Long: i = 1000
For colnum = 11 To 100 Step 4
    Range(Cells(2, colnum), Cells(225, colnum)).Formula = "=$D2*$G2*" & _
        Cells(2, colnum - i).Address(False, False)
    i = i - 1
Next colnum
L42
  • 19,427
  • 11
  • 44
  • 68
  • This definitely works! And real quick, let's say we didn't know how many Columns we have, what would I replace 100 or 1000 with, in the first line of code? – user3720702 Jun 10 '14 at 04:36
  • That's easy. [You can try this](http://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data). – L42 Jun 10 '14 at 04:49
  • Awesome! Lastly, if I want the portion of the code Cells(2, colNum - 2) to be changed to Cells(2, colNum - 1000) but have it where the number 1000 would decrease in increments of 1 as the code goes from one column to the next? like so.. Cells(2, colNum - 999) then Cells(2, colNum - 998) – user3720702 Jun 11 '14 at 02:22
  • @user3720702 Just add another variable counter which will decrement by 1. See my edit – L42 Jun 11 '14 at 02:26
  • It worked!! And could that portion of the code: Cells(2, colNum -i).Address(False, False) be squared and inside of an argument such as SQRT? For example .... .Formula = "=SQRT(D2^2+G2^2+ (Cells(2, colNum -i))^2 )? This is my last concern and is for another set of columns. Thanks again for all of your help! – user3720702 Jun 11 '14 at 02:48
  • @user3720702 Just construct your formula string correctly. `"=SQRT(D2^2+G2^2+" & Cells(2, colNum -i).Address & "^2)"` would works I guess. – L42 Jun 11 '14 at 03:10