Sum with R1C1 notation and variable

[chriscorpion786]

Hi ,

Can anyone tell me how to sum a range with R1C1 notation using a variable to go two cells left from the active cell, for eg:

Activecell.value = "=sum(rc[-2]:rc[-1] ' this works perfectly but when I use a variable I cannot make it work...

Activecellvalue = "=sum(rc & "[- lastcol +4 ]" :rc[-1] 'this does not work.

Multiple examples would be great if possible.

Thank you in advance.

 

[Danerida]

Try:

Activecell.value = "=sum(rc[- " & lastcol & " +4 ]:rc[-1]"

This will only work if lastcol is a number.
Dan

 

[chriscorpion786]

Hi Dan,

I tried the same , dosent work...

activecell.value = "=sum(rc [- " & lastcol & " +4]:rc[-1])"

lastcol is a number by the way. It throws up the error : Application defined or object defined error.

 

[Danerida]

activecell.value = "=sum(rc [- " & lastcol & " +4]:rc[-1])"

Probably should be:

activecell.formulaR1C1= "=sum(rc [- " & lastcol & " +4]:rc[-1])"

 

[Norie]

Try this.

ActiveCell.FormulaR1C1 = "=SUM(RC[" & -lastCol + 4 & "]:RC[-1])"