Forumula error in vba

[dantheman9]

HI,

can anyone help with this one?

ActiveCell.Formula = "=SUM((Range(Cells(0, k - 2))):(Range(Cells(0,1 +(k+1))))) & ""M"""

k is a varable integer

thanks

 

[Andrew Poulsom]

If you record a macro while entering your formula what code do you get?

 

[dantheman9]

ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])

...but the first cell location is not always the same (hence using k as a varable)

?

 

[Norie]

What exactly is Range(Cells(0, k - 2))):.... etc meant to refer to?

Even if you were trying to use that to get the address of a range to sum it wouldn't work because there is no row 0.

 

[dantheman9]

arh...that might well be where im going wrong then!!

Im trying to do the following;

=SUM(current row, Varable column : Current Row, -1 Column)

 

[Norie]

You might not actually need to do any of that.

Have you tried just using what's been recorded?

The formula in that code uses R1C1 relative referencing so the actual range that will be summed will be based on where the formula is entered.

Perhaps this might illustrate what I mean.

If I select D8 and run that code the resultant formula is SUM(B8:C8).

If I select C20 the formula is SUM(A20:B20).

So wherever you enter the formula the sum is of the 2 cells to the left.

 

[dantheman9]

HI Norie,

Yes i see what you mean, problem is that number of columns between the the location of the forumla changes (and so can vary from 2 columns to 10) all of which need to be included in the SUM.

Cheers

 

[Norie]

What exactly are you trying to sum?

This should let you specify the column (by number) in the formula.

ActiveCell.FormulaR1C1 = "=SUM(RC" & K & ":RC" & (K - 1) & ")"