Excel formulas in VBA

[Padmaja]

Hi,
I have to find the average of 3 numbers. These 3
numbers are entered in 3 different columns, and in the 4th
column their average is calculated.

Lets say, Column A ,B and C contain the numbers whose
average is to be found. D column contains the formula for
calculating the average.

eg. formula for D1 is "=(A1+B1+C1)/3"
then formula for D2 is "=(A2+B2+C2)/3"

Now if i insert a new row say at 9th place
It should automatically take the formula for D9 as
"=(A9+B9+C9)/3"

How is this to be done programatically?

Awaiting for your reply,
Thanks,
Padmaja

 

[GB]

Range("D8").Copy Destination:=Range("D9")

Geoff

 

[Padmaja]

Hi,
Thanks for the help.I would like to add another
question related to the previous one.How would i achieve
the same result when i do not know the number of rows
being inserted.
Basically it means that i would be inserting "n" rows
and would like to copy the formula for all the new "n"
rows programatically.

Thanks,
Padmaja

 

[Bob Phillips]

Padmaja,

Try

cRows = Cells(Rows.Count,"D").End(xlUp).Row
Range("D"&cRows).Copy Destination:=Range("D"&cRows+1)

--

HTH

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

 

[Dana DeLouis]

Here's one idea...

Sub Demo()
With [D9]
.EntireRow.Insert
.Offset(-1, 0).FillDown
End With
End Sub