Adjust an existing formula based on most recently added column and prior month end date using VBA

Question

I'm currently building a macro to update an inventory file that's updated weekly. Once all the new data has been added for the week, I need to adjust the formula in the totals column ("MTD Chg") to pull from the last week of the prior month and the newest column added. I've been able to select the correct cells in the "MTD Chg" column, but now I'm stuck due to the "MTD Chg" and prior month date column ranges being dynamic.

Sub try_to_update_formula()
Sheets("Summary").Select
    Cells.Find(What:="MTD Chg", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
 Dim rwRng As Range, curCol As Long
    curCol = ActiveCell.Column
Set rwRng = Union(Cells(51, curCol), Cells(52, curCol), Cells(53, curCol), Cells(54, curCol), Cells(58, curCol), Cells(59, curCol), Cells(60, curCol), Cells(61, curCol), Cells(62, curCol), Cells(63, curCol), Cells(64, curCol), Cells(65, curCol), Cells(68, curCol), Cells(69, curCol))
    rwRng.Select

The above code searches for the column with "MTD Chg" and selects the called-out cells in that column - "The MTD Chg" columns range will change every week when the new current week column is inserted to the right. In the photo attached I need to update the formula to pull from N51 instead of M51; The new formula in cell O51 would be =L51-N51. Another issue I'm going to run into as the month changes is having the formula update to pull from the last week of the prior month. Ex: When updating the file in the first week of April the "MTD Chg" column formula would need to be "Last week of March column - 1st week of April column".

Any advice on how to accomplish this? Please let me know if you need more information.

example of what my sheet looks like

UPDATE:

I was able to figure out how to get the second part of the formula to adjust to the newly added column!

rwRng.Formula = "=L51-RC[-1]"

I'm still unsure of how to get the formula to pull from the last week of the prior month column when the new month starts - i.e. if the current month is March then the last week of February column needs to be the "L51" cell, then in April the last week of March column needs to be the "L51" cell.

Answer 1

Two parts to the answer:

• R1C1 formulae will do wonders.
• You want to determine where the last month is in relation to Mtd Chg.

I generated the below code for testing purposes, though I did use dot notation for your existing code to correct the unqualified .Cells() references.

 With Sheets(1)
        Dim MtdChgCell As Range:  Set MtdChgCell = .Cells.Find("MTD Chg")
        Dim MtdChgCol As Long:  MtdChgCol = MtdChgCell.Column
        Dim MtdChgRow As Long:  MtdChgRow = MtdChgCell.Row
        Dim rwRng As Range:  Set rwRng = Union(.Cells(51, MtdChgCol), .Cells(52, MtdChgCol), .Cells(53, MtdChgCol), .Cells(54, MtdChgCol), .Cells(58, MtdChgCol), .Cells(59, MtdChgCol), .Cells(60, MtdChgCol), .Cells(61, MtdChgCol), .Cells(62, MtdChgCol), .Cells(63, MtdChgCol), .Cells(64, MtdChgCol), .Cells(65, MtdChgCol), .Cells(68, MtdChgCol), .Cells(69, MtdChgCol))
        'Dim rwRng As Range:  Set rwRng = .Cells(MtdChgRow + 1, MtdChgCol) 'for testing
        Dim deltaRow As Long:  For deltaRow = MtdChgCol - 1 To 1 Step -1
            If Month(.Cells(MtdChgRow, MtdChgCol - 1).Value) - Month(.Cells(MtdChgRow, deltaRow).Value) = 1 Then Exit For
        Next deltaRow
        rwRng.Formula = "=RC" & deltaRow & "-RC[-1]"
    End With

Edit: Changed rwRng.Formula = "=RC[-1]-RC" & deltaRow to rwRng.Formula = "=RC" & deltaRow & "-RC[-1]".