CODEWITHSUNDEEP
excelvba
Amine Chentouf
Amine Chentouf

Reputation: 71

VBA - Change formulas in a range

So i would like to know how i could update the formulas in a range (E7:BE7), in my sheet i have multiple tables so i can't update it with lastRow.

For each cell in the range E7:BE7 i have these formulas :

after using a script to add a new entry to the table i would like to update the formulas so that the last row is incremented by one. So i would have all of the formulas in that range become like this after a new entry :

Here's a snippet of my script that adds a new entry, i would like to automatically update those formulas, but i'm still new to vba and struggling a bit, any help would be appreciated.

        With Target
            Dim cell As Range
            Dim calendar_header As Range
            
            Set calendar_header = Range("E7:BE7")
            
            ' this is the part that will insert a new row on my target and copy the information i need in the row
            .Offset(0).EntireRow.Insert Shift:=xlDown
            .Offset(-2).EntireRow.Copy
            .Offset(-1).EntireRow.PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            Cells(.Row - 1, "A").Value = Cells(.Row - 2, "A").Value
            Cells(.Row - 1, "B").Value = Cells(.Row - 2, "B").Value
            Cells(.Row - 2, "E").Copy
            Cells(.Row - 1, "E").PasteSpecial
            Application.CutCopyMode = False

            ' This is the loop that's supposed to make my updates on the formulas
            For Each cell In calendar_header
                
                 ' Im struggling with this part.
                
            Next cell
            
            
        End With

Upvotes: 0

Views: 679

Answers (1)

Zac
Zac

Reputation: 1944

You don't need a loop to update the formula. Presuming that your Target is the last row, you could just do this: calendar_header.Formula = "-SUM(E$8:E$" & Target.Row & ")". This will update the formula in your range

NOTE: Also consider what happense if Target has more than 1 cell

Upvotes: 1

Related Questions