Problem with using formula

[sameerce]

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

 

[Pete McCosh]

Sameer,

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

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

Cheers, Pete

 

[Bob Phillips]

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)

 

[Wouter]

Hi,

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

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

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

Succes,

Wouter

 

[Tom Ogilvy]

Range("A33").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.