Entering a Formula using VBA in a Cell

Question

I have a table in a spreadsheet that I refresh daily. The number of rows in the worksheet changes from day to day and the formulas that I currently have in place to count up the items in the table do not include any new rows of data (if any) that are added to my table after a refresh.

I wanted to create a macro that would automatically update these formulas to include all rows in the table. The code below is my attempt. The code works just fine in terms of calculating the correct values, but it is not entering a formula into the cell. It is only entering in the result of the formula.

How can I change the code to enter the formula into the cell and not just the value?

Sub formula_update()

ThisWorkbook.Activate
    Worksheets("Summary").Activate
        Range("F9").Select
        
        Do Until IsEmpty(Selection)
            ActiveCell.Formula = Application.CountIfs(Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(3, 0).End(xlDown)), "<>0")
            ActiveCell.Offset(0, 1).Select
        Loop
        
End Sub

You need to enter a formula (as a String): `ActiveCell.Formula = "=COUNTIFS(...)"`. — BigBen, Aug 05 '21 at 16:59
Also see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). `Activate` and `ActiveCell` should be avoided as well. — BigBen, Aug 05 '21 at 17:00
If you format the dataset as a table using `CTRL` + `T` you can name the range and the formulas will actually scale with the size of the table, so you can just avoid VBA altogether. For example, `=Counta( YourTable[ColumnA] )` — Mark S., Aug 05 '21 at 17:46
@BigBen Do you have an example you could share of how to do this with a changing range? My table constantly has rows being added/taken away. Formulas won't automatically update to include new rows I add hence I wanted the macro to recreate the formulas for me. How to I input the formula as a string and still capture what the offsets and end(xldown) are doing for me? — DiYage, Aug 06 '21 at 17:28

Filippo Corsini · Answer 1 · 2021-08-05T18:27:02.900

0

Sub formula_update()

ThisWorkbook.Worksheets("Sheet4").Range("A3").Select
        
        Do Until IsEmpty(Selection)
            ActiveCell.Value = Application.CountIfs(Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(3, 0).End(xlDown)), "<>0")
            ActiveCell.Offset(0, 1).Select
        Loop
        
End Sub

edited Aug 05 '21 at 18:27

answered Aug 05 '21 at 18:19

Filippo Corsini

1
1

Unfortunately this didn't change anything. It's still only inputting the value of the formula into the cell and not the formula itself. Any other ideas? – DiYage Aug 06 '21 at 17:25

Entering a Formula using VBA in a Cell

1 Answers1