macro which can change the inside of a formula

[excelNewbie22]

hi,
can anyone help me out with a macro that change a range in a cell?
like if a1000 is:
=a90:$g$90
i want the macro to change it to:
=a89:$g$90
and so on, subtract 1 every macro run (a88 then a87....)
?

 

[Peter_SSs]

For that particular example, try this

Sub Change_Formula()
  Dim Bits As Variant
  
  With Range("A1000")
    Bits = Split(.Formula, ":")
    .Formula2 = Left(Bits(0), 2) & Mid(Bits(0), 3) - 1 & ":" & Bits(1)
  End With
End Sub

 

[excelNewbie22]

thanks peter, works well!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.