How to create a formula without $ sign using VBA?

[Rojo Habe]

I need to sum a non-constant range and then autofill the resulting formula down the page. Finding the range is no problem; a small Do... While loop takes care of that for me, and gives me the range Cells(2,Start) to Cells(2,Finish).

The following line

.FormulaR1C1 = "=SUM(Weekly!R2C" & Start & ":R2C" & Finish & ")"

Places the formula into the spreadsheet as

=SUM(Weekly!$O$2:$R$2)

but I need it without the $ signs so that I can autofill it into the next 120 rows.

This is my second post today for something that will no doubt be incredibly simple once you know how! Excuse me for being thick but I'm still learning.

 

[Joe4]

Not as easy as you might think.

Here is some code that fixes it "after the fact". The code I wrote takes the formula from the ActiveCell and removes all the "$" from it. Hopefully, you should be able to adapt it to work for you.

    Dim myFormula As String
    Dim myNewFormula As String
    myFormula = ActiveCell.Formula
    myNewFormula = Application.WorksheetFunction.Substitute(myFormula, "$", "")
    ActiveCell.Formula = myNewFormula

There may be a more efficient way of doing it, but it really depends on how the whole loop of your code works.

 

[Andrew Poulsom]

Another way:

.FormulaR1C1 = Application.ConvertFormula("=SUM(Weekly!R2C" & start & ":R2C" & finish & ")", xlR1C1, , xlRelative)

 

[Joe4]

Andrew,

Not just "another way", but from the looks of it, a better way.
I did not know about "ConvertFormula". That is a good one to remember!

 

[Rojo Habe]

ADVERTISEMENT

This is a great site! Every time I get stuck, someone always has the answer. Thanks, guys. I may even get this project finished today, at this rate.

Only one slight problem: Using Application.ConvertFormula seems to offset the row number by one. Where I used to get $O$2:$R$2 it now gives me O3:R3. Not a biggy, I can adjust this in the code, but I was just wondering what would cause this.

 

[Andrew Poulsom]

The outcome depends on which cell contains the formula. You may need to add a RelativeTo argument:

.FormulaR1C1 = Application.ConvertFormula("=SUM(Weekly!R2C" & start & ":R2C" & finish & ")", xlR1C1, , xlRelative, .Cells(1, 1))

 

[Rojo Habe]

That did it. Thanks, Andrew.