VBA .Formula using Cells(r,c) to identify ranges creates absolute references - How to create relative instead

[psulion01]

Hopefully this is a simple one...

Cells(LastRow + 1, c).Formula = "=SUM(" & Cells(7, c).Address & ":" & Cells(LastRow, c).Address & ")"

That line of code produces a formula in the target cell along the lines of "=SUM($A$7:$A$10)"

The problem is that later in my code I run a horizontal sort and afterwards all of the formulas are wrong because the absolute column references don't change with the sort (i.e. column A is now Column C, yet the formulas in Column C are all still "=SUM($A$7:$A$10)"

Is there a way around this using my existing code, or do I need to utilize R1C1 or something like that? If so can someone assist with the line above?

Thanks!
Mike

 

[Asator]

.formulaR1C1

Basically your formula will look like "=sum(R7C" & c & ":R" & lastrow & "C" & c & ")"

 

[psulion01]

Thanks... no other way to use the Cells(r,c) method? R1C1 always seems so hard to read...

Appreciate the fast response!

edit:
Just tried it and it seems like the same problem persists. Calcs are right until the sort, then things are all out of place. Can I somehow force relative references for the columns?

 

[Asator]

Not that I'm aware of...

 

[psulion01]

[SOLVED] Re: VBA .Formula using Cells(r,c) to identify ranges creates absolute references - How to create relative instead

Found it... very simple.

Cells(LastRow + 1, c).Formula = "=SUM(" & Cells(7, c).Address(False, False) & ":" & Cells(LastRow, c).Address(False, False) & ")"

 

[Asator]

Re: [SOLVED] Re: VBA .Formula using Cells(r,c) to identify ranges creates absolute references - How to create relative instead

We both learned something then

 

[shg]

This is one of those cases that begs for an R1 formula:

Cells(LastRow + 1, c).FormulaR1C1 = "=SUM(R7C:R[-1]C)"