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

 

[hrayani]

no its not working dear

let me explain....

This is the code u provided me ..... its the 1st code

x = Right(Range("AG3").Formula, Len(Range("AG3").Formula) - 2)
Range("AG3").Value = "=U" & x + 1
End Sub

Now in cell AG3 i have the formula =u2 and when i click the command button ( macro attached to it ) once then it becomes u3 then u4 then u5 and so on....

But as soon as the formula in cell AG3 in changed from =U3 to V3 then i have to make the changes in the code also...
this part of the code

Range("AG3").Value = "=U" & x + 1

i have to change the (U) to (V)
and i have to do it everytime when the formula in AG3 changes....

See, the code is working just perfect but evertime the change has to be made in the code in order for the code to give the correct output..

What i am trying to say is ( IF POSSIBLE ) that the code should pick the column address directly from the cell so that everytime i dont have to change the column address in the code..

For Example

If in cell AG3 ----> =u2 then u3,u4,u5 and so on
If in cell AG3 ----> =v2 then v3,v4,v5 and so on
If in cell AG3 ----> =w2 then w3,w4,w5 and so on

what currently i am doing is i am changing (u) to (v) and (v) to (w) in the code also alongwith the cell...

pls let me know if there is anything yet to be cleared...

Sorry for the trouble

Awaiting reply,

Humayun

 

[villy]

Clear now try this

Sub RowHv()
fr = Range("AG3").Formula
fr = Left(fr, Len(fr) - 1)
x = Right(Range("AG3").Formula, Len(Range("AG3").Formula) - 2)
Range("AG3").Value = fr & x + 1
End Sub

 

[hrayani]

Dear Villy,

First of all sorry for a late reply as there was internet connectivity problem.

dear now the previous problem is solved... i dont have to change the code everytime i make the change in the cell...

But....

a new problem has arise....

in AG3 i have =U2

when i press the click button ( macro attached to it ) it works ok until (U10) and after U10 it directly goes to (U111) instead of (U11)

do u have any idea why its going directly to 111 instead of 11 ???

Awaiting reply,

Regards,

Humayun

 

[villy]

Hi there,
Sorry just replying now, been busy for weekend..
Anyway I found the problem and it should be working now with this one..

Sub RowHv()
fr = Range("AG3").Formula
x = Right(Range("AG3").Formula, Len(Range("AG3").Formula) - 2)
fr = Left(fr, Len(fr) - Len(x))
Range("AG3").Value = fr & x + 1
End Sub

 

[Peter_SSs]

Since your AG3 formula appears to to be a simple formula, just drawing a value from another cell, you could also try this alternative.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RowHv()<br>    Range("AG3").Formula = "=" & Range("AG3").Precedents.Offset(1).Address(0, 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[villy]

It was really a nice formula Mr. Peter I will highly recommend this one.
Thanks

 

[Peter_SSs]

It was really a nice formula Mr. Peter I will highly recommend this one.
Thanks

Thanks.

One other thing I forgot to mention is that this code is somewhat prone to failure. If rows or columns are added/deleted above/left of cell AG3, the code will fail (or possibly worse still alter an inintended cell). A better alternative would be to name the cell AG3 as, say, fCell then use this line in my code

Range("fCell").Formula = "=" & Range("fCell").Precedents.Offset(1).Address(0, 0)

That way, the code should continue to act on the correct cell even in the advent of row/column changes.

 

[hrayani]

Dear Villy,

Thanx brother....... now the code is working JUST PERFECT....

pls if you can also provide me the code for the columns rather ammend the code which u last provided, i should say.

This One......

i = Range(Range("AG20").Formula).column
i = Cells(2, i + 1).Address
x = Mid(i, 2, Len(i) - 3)
Range("AG3").Value = "=" & x & "2"

the problem with this code is the same as there was with the previous code...

For Example...

In CEll AG3 i have =U3

after clicking the button ( macro attached to it ) it should be like V3,W3,X3 and so on

But, i this part of the code needs to be changed everytime for the row #

Range("AG3").Value = "=" & x & "2"

hope u understand...

Regards,

Humayun

 

[hrayani]

Dear Peter Sss,

Thanks for the code u provided...

I tried your one too and its working perfect...

Thanks once again

Regards,

Humayun

 

[Peter_SSs]

In CEll AG3 i have =U3

after clicking the button ( macro attached to it ) it should be like V3,W3,X3 and so on

As I suggested in post #17, it would be better to name cell AG3 as "fCell" and then you could have your button run this code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> RowHv()<br>    Range("fCell").Formula = "=" & Range("fCell").Precedents.Offset(, 1).Address(0, 0)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Notes:
1. If you need help with how to name the cell, post back with which version of Excel you are using.

2. If you don't want to give AG3 a name to use in the code then just replace "fCell" in the code with "AG3".