Repeating Formula with Incrementing Cell Reference

1
votes

So I have built a formula that has Absolute Cell References, and wanted to repeat the same formula down 3000 cells with each one referencing increment cells. (1st formula referring to Cell $A$1, 2nd formula referring to $A$2) I know that I could easily do this without referencing exact cells and the Fill Handle and this is currently how it's set up, however there's a very large number of people who work in this spreadsheet that have bad Excel manners, and regularly delete rows and cells or copy and paste, which breaks the formulas.

Rather than manually editing the same formula in each cell to change the references from relative to absolute, I was wanting to run a Macro to automatically run the formula for 3000 cells.

I had at first built a Macro that fills 20 cells with the formula, but it didn't adjust the formula based on the active cell. (Always entered with range $A$1:$A$20, and not $A$21:$a$40 when started further down) I changed the Macro to loop, but it looks with all formulas referencing $A$1 rather than updating.

The Macro set up to loop is as follows:

Sub HDDatesRef()

ActiveCell.Select
ActiveCell.FormulaR1C1 = "=IF(AND(HD!R1C1>0,ISBLANK(HD!R1C4)),HD!R1C1,""n/a"")"
ActiveCell.Offset(1, 0).Range("A1").Select

Loop Until ActiveCell.Value = ""

End Sub

Any and all help with figuring this out would help immensely. Right now I also have access to Liknedin Learning, so if there's any suggestions for courses on there I should look into so I can understand what I need to do will help with this.

2
excelvba

2 Answers

0
votes

The Excel application object has a function called ConvertFormula which you can use to change a formula between reference styles (A1 or R1C1) and to specify whether the rows and columns should be relative references or absolute references.

If you start off by creating the formula in each row as a relative reference then you can use ConvertFormula to turn it into an absolute reference. The only restriction is that the formula cannot be longer than 255 characters.

Adapting your code and following the advice in How to avoid using Select in Excel VBA gives us:

Option Explicit

Sub HDDatesRef()

Dim r As Range

' If we know the cell address we want to start in then we could use that directly
' e.g. Set r = Worksheets("HD").Range("E1")
Set r = ActiveCell

Do
    ' The With block just saves us typing r.FormulaR1C1 multiple times
    With r
        ' Don't know what your relative formula would be. I've assumed that we are
        ' working in column E but adjust as appropriate
        .FormulaR1C1 = "=IF(AND(HD!RC[-4]>0,ISBLANK(HD!RC[-1])),HD!RC[-4],""n/a"")"

        ' Take the formula we already have which is in R1C1 format, keep it in R1C1 format,
        ' change it from a relative reference based on cell r to an absolute reference
        ' and make that the new formula for this cell
        .FormulaR1C1 = Application.ConvertFormula(.FormulaR1C1, xlR1C1, xlR1C1, xlAbsolute, r)
    End With

    ' Move down one row
    Set r = r.Offset(1, 0)
Loop Until r.Value = ""

End Sub

In case you aren't familiar with them. here are the references for Option Explicit and With...End With

0
votes

You can do this without looping, Excel is smart enough to know you want incremental.

As an example do run this on a fresh sheet:

Sub ShowIncremental()
    Range("A1:A10").Formula = "=Row(A1)"
    Range("B1:B10").Formula = "=A1*2"
    Range("C1:C10").Formula = "=sum(B$1:B1)"
End Sub

Notice the formulas created in A1:C10. Notice Excel incremented them even though the code didn't say to except in the case where we absoluted B$1.

I recommend you do something similar with your code to avoid looping, this will be much much faster.