how can I get a vba to change a formula when changing cells

[dissertationhelp]

Hi, I have no idea how to do a VBA, but got one online which nearly does the job. Basically i have 2000 columns for 4000 rows, so manual copy and paste will take days. I have a blank column between each which i need a basic formula inserted. EG in cell D4 i need (C5-C4)/C4. The macro i have does this but it does not update the formula for D6 where i will need (C6-C5)/C5 and so on so forth instead it copies (C5-C4)/C4 into all the blank cells i need the formulas in? Can anyone help me please?
This is the macro i am running at the moment:

Sub fcopy()
Dim r, c As Integer
For r = 4 To 55
For c = 4 To 20 Step 2
Cells(r, c).Formula = "=(c5-c4)/c4"
Next
Next
End Sub



Thanks for any replies!

[ek3040]

Not the fastest way to do it but try replacing:

Cells(r, c).Formula = "=(c5-c4)/c4"

with:

Cells(r, c).Formula = "=(" + Cells(r, c - 1).Address + "-" + Cells(r - 1, c - 1).Address + ")/" + Cells(r - 1, c - 1).Address

[Brendan_Floyde]

You need to use relative referencing - something like:

Please Login or Register  to view this content.

Easiest way to get to grips with this is record a macro and use the "use relative references button"

[protonLeah]

title updated: