Using VBA to update automate a formula

Hi all, if I have a simple formula such as =a1, can I use VBA to update it to a2?

Would really appreciate the help

What do you mean when you say update? It sounds like you want to copy the value from A1 into A2.

No, I basically just want the formula "=A1" to change to "=A2", can that be done using VBA

Perhaps something like this?

Option Explicit

Sub Change()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If cell.Formula = "=A1" Then
    cell.Formula = "=A2"
End If
Next

End Sub

Alf

Thats great, just one slight problem though, if I insert the code into a macro button, how can I make sure that everytime I click the macro button, the cell changes, for example if I click it once then the cell changes from A1 to A2, if I click the button again then the cell changes to A3, is there a way to do that?

You could try this macro then. Have not done any extensive testing but the way it's written it should work up to A10.

Option Explicit

Sub UpdateForm()
Dim cell As Range

ActiveSheet.UsedRange.Replace What:="=", Replacement:="'="

For Each cell In ActiveSheet.UsedRange

If InStr(cell, "=A") > 0 And Len(cell) = 3 Then
     cell.Formula = "=A" & Right(cell, 1) + 1
End If

Next

End Sub

Alf