VBA code to update formulas with cell references on another tab

[BillPeterson]

Ok I have a macro which copies a tab, and then I want it to update formulas. In A1 on the freshly created tab, I have the following formula:

=Budget!S13

How can I alter this formula with VBA code to decrement the row number?

When it's done I want it to be:

=Budget!S12

 

[JoeMo]

Does this do what you want?

Sub BillPeterson()
Dim x, y
'Copy a tab - change sheet name to suit
Sheets("Sheet1").Copy after:=Sheets("Sheet1")
With ActiveSheet.Range("A1")
    x = .Formula
    If InStr(1, x, "Budget!S") > 0 Then
        y = Split(x, "S")(1) - 1
        If y >= 1 Then
            .Formula = "=Budget!S" & y
        End If
    End If
End With
End Sub

 

[BillPeterson]

Thank you! I will give that a shot. The following worked, but I'm sure there's a 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

I should figure out how to convert the above into a loop so it will work for multiple cells, allowing me to easily expand the range if necessary.

 

[JoeMo]

Thank you! I will give that a shot. The following worked, but I'm sure there's a 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

I should figure out how to convert the above into a loop so it will work for multiple cells, allowing me to easily expand the range if necessary.

You are welcome- thanks for the reply.