Change Formula Range By Cell Click

[hrayani]

Hello,

In cell B1 i have the Formula =A1

What i want is when i click the command button the the range A1 should change to A2 and then range A3 and so on...

Pls if anyone could help...

Regards,

Humayun Rayani

 

[villy]

You can change to this one..

Sub ColV()
iCol = Range("AG3").Formula
iCol = Range(iCol).Offset(0, 1).Address
x = WorksheetFunction.Substitute(iCol, "$", "")
Range("AG3").Value = "=" & x
End Sub

 

[hrayani]

Dear Peter Sss,

Bundle of Thanks....

Thanks for both the codes and yes i have named the cells to ignore any possible mishap or an error i should say.....

Thanks once again

Regards,

Humayun Rayani

 

[hrayani]

Dear Villy,

Thanks for your continous support......

i tried your last code but unfortunately its giving me runtime error 1004..

i guess we should stick to the code which Peter Sss provided..

Once again thanks for all your replies...

Best Regards,

Humayun

 

[villy]

No problem I guess it's a bit better from the expert.
Thanks