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]

Try:

Sub va()
x = Right(Range("B1").Formula, Len(Range("B1").Formula) - 2)
Range("B1").Value = "=A" & x + 1
End Sub

 

[hrayani]

Thanks Villy...

The code u provided is working perfect...

pls if you could also let me know what to do if i want to change the range in horizontal position instead of vertical..

i mean... instead of from a1, to a2,a3,a4 it should change to ( from a1 to b1,c1,d1 and son on )

Regards,

Humayun Rayani

 

[villy]

This one:

Sub va()
i = Range(Range("B2").Formula).Column
i = Cells(1, i + 1).Address
x = Mid(i, 2, Len(i) - 3)
Range("B2").Value = "=" & x & "1"
End Sub

 

[hrayani]

Dear Villy,

Thanks for the help

there is bit of problem with the code u just provided for the columns...

For example

In cell A1 i have B25 ..... so with each click it should be like c25,d25,e25 and so on...... whereas, with the first click its adding the column but the range is c1 instead of c25.... its starting from the first row... the row number should be the same...

Awaiting reply

Humayun Rayani

 

[hrayani]

And yes one more thing with the previous code if possible.

Sub va()
x = Right(Range("B1").Formula, Len(Range("B1").Formula) - 2)
Range("B1").Value = "=A" & x + 1
End Sub

Everytime i have to change the Column Address in the the code also whenever i am changing the address in the cell

Range("B1").Value = "=[B]A[/B]" & x + 1

What i am trying to say, is it possible for the code to pick the column address from the cell instead of changing it everytime in the code

 

[villy]

In cell A1 i have B25 ..... so with each click it should be like c25,d25,e25 and so on...... whereas, with the first click its adding the column but the range is c1 instead of c25.... its starting from the first row... the row number should be the same...

 Sub va()
i = Range(Range("A1").Formula).Column  'change as needed
i = Cells(25, i + 1).Address
x = Mid(i, 2, Len(i) - 3)
Range("A1").Value = "=" & x & "25"  'change as needed
End Sub

you can change values for red

 

[hrayani]

Dear Villy,

Both the codes are working ok but evertime i have to change the column address and the row address in the code alongwith the cells...

The 1st code requires the column address to be changed in the code
The 2nd code requires the row address to be changed in the code

Regards,

Humayun

 

[villy]

What i am trying to say, is it possible for the code to pick the column address from the cell instead of changing it everytime in the code

How do you want o pick up your col address? As long as your col to be picked has a formula of ex. =A1 otherwise it will be error

 

[villy]

Check:

Sub RowH()
cell = Selection.Address
i = Range(cell).Column
x = Right(Selection.Address, Len(Selection.Address) - 3)
col = Cells(x, i + 1).Address
col = Mid(col, 2, Len(col) - 3)
Range("B1").Value = "=" & col & x
End Sub

Select a cell then in B1 it will output the value of the cell next to selection
Ex. you select C3 then run the macro then B1=value of D3
Is that make sense?