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

[healeydm]

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.

 

[healeydm]

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.

View attachment 109368

UPDATE:

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

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.