Using VBA to update formulas to reference last month's tab

[BillPeterson]

I have VBA code to copy a template tab every month and now want it to also update several formulas. Here are 2 formulas with a description of the desired outcome immediately above each formula.

#1: The formula below is in Cell A1, and I want VBA code to make it point to one cell up on the same "Budget" tab.... i.e. a relative offset so regardless of the row number it reduces the value by 1... which would change S12 to S11:

=Budget!S12

#2: The formula below is in Cell R8, and I want VBA code to make it point to "Jun" instead of "May", "Jul" instead of "Jun" and so on.... i.e. always increment the month and if necessary, the year "24Jan" instead of "23Dec":

=CC23May!B1+A1

Thanks in advance for your help!

 

[Tetra201]

See if the following lines of code work for you:

With Sheets("your_sheet_name").Range("A1")
    .Formula = "=Budget!S" & Month(DateAdd("m", -1, "2000-" & Mid(.Formula, 10) & -1))
End With
With Sheets("your_sheet_name").Range("R8")
    .Formula = "=CC" & Format(DateAdd("m", 1, 20 & Application.Replace(Mid(.Formula, 4, 5), 3, 0, "-") & -1), "yymmm") & "!B1+A1"
End With

 

[BillPeterson]

Nice!, The formula in A1 was updated correctly (points to S11 as desired).

However the formula in R8 appears to have skipped 2 months instead of just 1 (want it to be "Jul")

=CC23Aug!B1+A1

I think this was my fault by saying the original formula pointed to May, when in actuality it was pointed to Jun. Sorry about the mislead there. Based on that which field should change?

Any suggestions? By the way, since I do this around the 2nd week of the current month (Jun) to prepare for next month (Jul), the code can be relative to today's date if that helps simplify things, although I'm curious to know the difference if any exists.

You guys are freaking awesome! Thank you so much for the help!

 

[BillPeterson]

Correction, what you had works perfectly, and I figured out my mistake. Thanks again!

 

[BillPeterson]

Ok Tetra, I ran into a little snag with a particular formula which has what I call a "cashback offset". It looks like this:

=Budget!U12+0.001

The code I have based on what you provided currently looks like this (basically identical to what you provided):

    With Sheets(sheetNameNew).Range("A3")
        .Formula = "=Budget!S" & Month(DateAdd("m", -1, "2000-" & Mid(.Formula, 10) & -1))
    End With

It seems to be choking on the "+0.001" piece and gives the following error:

When I click Debug it highlights the ".Formula...." line. How would you adjust it to handle this? By the end of the month when I want to run the code, it's usually "+12.34" or so, but when it's copied I'd like to reset it to "+0.001". The reason for this is it reminds me to update it to match whatever cashback amount the credit card website shows after they generate their statement.

Thanks again!

 

[BillPeterson]

another snag I've found is if the new formula points to a cell which hasn't been formatted yet, it barfs the same error for some reason

 

[BillPeterson]

lol this seem to work, but there's probably a much simpler way....

    Dim strFormula As String
    Dim Leftpart As String
    Dim RowValue As Long
    
    strFormula = Range("A1").Formula
    RowValue = Range(strFormula).Row
    RowValue = RowValue - 1
    Leftpart = Left(strFormula, 9)
    strFormula = Leftpart & RowValue
    Range("A1").Formula = strFormula