PDA

View Full Version : [SOLVED] Macro to fill formula in reverse



Sir Babydum GBE
06-09-2015, 01:00 AM
Hello, nice to be back...

i want to use VBA to fill formulas in an opposite direction. That's really badly put, so I'll try again.

suppose I decide to input in A1 "=c1000*2"

now if I copy down to A2, my formula will be =C1001*2

but I want it to read =C999*2, and A3 =C998*2, and so on

so basically I'm looking for VBA to look at the row reference in A1 then copy down to (in this example) A1000 until the macro realises its at =C1*2 and then stop.

is this possible?

Thanks in advance

mancubus
06-09-2015, 01:38 AM
Hello.

try this:



Sub vbax_52839()

Dim i As Long, j As Long

j = 1000

For i = 1 To 1000
Range("A" & i).Formula = "=C" & j & "*2"
j = j - 1
Next i

End Sub

gmayor
06-09-2015, 01:38 AM
Maybe something like


Dim i As Long, j As Long
If IsNumeric(Range("A1")) Then
j = val(Range("A1"))
For i = 2 To j
j = j - 2
Range("A" & i).FormulaR1C1 = "=R[" & j - 1 & "]C[2]*2"
Next i
End If

Sir Babydum GBE
06-09-2015, 11:32 AM
Just got back in. Will try these later. Thanks!

Aflatoon
06-10-2015, 02:39 AM
Why not just change the formula to say:
=INDEX(c:c,1001-row())*2
and fill down normally?

Sir Babydum GBE
06-13-2015, 02:10 AM
Thanks guys for the suggestions - briliant!