Hi,
I want to incorporate formulas to different cell on command butto
click. The formula is common for different columns just the position o
the cells value to be used shifts by one column.

To explain, say I have a formula for cell A3 which is A3=A4+A5
Similary, I want to use this formula for the next cells like B3=B4+B
and so on for C,D.

Instead of writing the same formula every time, I want to make thi
dynamic by changing the value of A to B, C and so on.

I tried using Range((Chr(Asc("A") + i)) & iCounter).value by puttin
this stmt in a for loop for i. But, this thing when put in the formul
does not works.

Can anyone suggest a solution to this problem.

Thanks,
Samee

--
Message posted from http://www.ExcelForum.com

Sameer,

cells(3,1).formula = "=A4+A5"
Will create the first formula, if required.

cells(3,1).copy destination:= range("B3:D3")
Will copy the formula relatively, just change the
destination range as necessary.

Cheers, Pete

-----Original Message-----
Hi,
I want to incorporate formulas to different cell on
command button
click. The formula is common for different columns just
the position of
the cells value to be used shifts by one column.

To explain, say I have a formula for cell A3 which is
A3=A4+A5.
Similary, I want to use this formula for the next cells
like B3=B4+B5
and so on for C,D.

Instead of writing the same formula every time, I want to
make this
dynamic by changing the value of A to B, C and so on.

I tried using Range((Chr(Asc("A") + i)) & iCounter).value
by putting
this stmt in a for loop for i. But, this thing when put
in the formula
does not works.

Can anyone suggest a solution to this problem.

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

.

Sameer,

Try

Range(Chr(65+ i) & iCounter)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sameerce " wrote in message
...
Hi,
I want to incorporate formulas to different cell on command button
click. The formula is common for different columns just the position of
the cells value to be used shifts by one column.

To explain, say I have a formula for cell A3 which is A3=A4+A5.
Similary, I want to use this formula for the next cells like B3=B4+B5
and so on for C,D.

Instead of writing the same formula every time, I want to make this
dynamic by changing the value of A to B, C and so on.

I tried using Range((Chr(Asc("A") + i)) & iCounter).value by putting
this stmt in a for loop for i. But, this thing when put in the formula
does not works.

Can anyone suggest a solution to this problem.

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

Hi,

If the cell you want to fill are connecting you can try this

Range("A3").Formula = "=A1+A2"
Range("A3:D3").FillRight

for non connecting cells you can eihter copy one by one or use
Range(...).FormulaR1C1 = "=R[-2]C+R[-1]C"

Succes,

Wouter

sameerce wrote in message ...
Hi,
I want to incorporate formulas to different cell on command button
click. The formula is common for different columns just the position of
the cells value to be used shifts by one column.

To explain, say I have a formula for cell A3 which is A3=A4+A5.
Similary, I want to use this formula for the next cells like B3=B4+B5
and so on for C,D.

Instead of writing the same formula every time, I want to make this
dynamic by changing the value of A to B, C and so on.

I tried using Range((Chr(Asc("A") + i)) & iCounter).value by putting
this stmt in a for loop for i. But, this thing when put in the formula
does not works.

Can anyone suggest a solution to this problem.

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/

Range("A3:D3").Formula = "=A4+A5"

will do what you describe. No need to enter the formula and copy it or to
loop and adjust the formula yourself - do it all in one step and let excel
manage the addresses by using relative references.

--
Regards,
Tom Ogilvy

"sameerce " wrote in message
...
Hi,
I want to incorporate formulas to different cell on command button
click. The formula is common for different columns just the position of
the cells value to be used shifts by one column.

To explain, say I have a formula for cell A3 which is A3=A4+A5.
Similary, I want to use this formula for the next cells like B3=B4+B5
and so on for C,D.

Instead of writing the same formula every time, I want to make this
dynamic by changing the value of A to B, C and so on.

I tried using Range((Chr(Asc("A") + i)) & iCounter).value by putting
this stmt in a for loop for i. But, this thing when put in the formula
does not works.

Can anyone suggest a solution to this problem.

Thanks,
Sameer


---
Message posted from http://www.ExcelForum.com/