Copying formula to top of a table which varies in size
Posted by Ben on October 26, 2000 12:16 PM
How can I use a macro to copy a formula in the last row of column G in a table, all the way up the table to the first row in column G, so the entire column in the table contains the same formula, but for the specific row.
e.g. =D7+D7 with the row above reading =C7+C8
But the catch is that the table varies in size from the first macro that will be run.
There are also more columns after column G!
Cheers
Posted by Ivan Moala on October 27, 2000 2:46 AM
Assuming the top cell column is G1 then
the following 2 should work to fill the column/s
with your formula.....
Sub Autofill_1Column()
Dim lcell As String
lcell = Range("G1").End(xlDown).Address
Range(lcell).AutoFill Destination:=Range("G1:" & lcell), Type:=xlFillDefault
End Sub
Sub Autofill_MultiColumn()
Dim DataRg As String
Dim lcell As String
Dim Tcell As String
lcell = Range("G1").End(xlDown).Address
Tcell = Range(Range("G1").End(xlToRight), Range("G1").End(xlDown)).Address
DataRg = Range(Range(lcell), Range(lcell).End(xlToRight)).Address
Range(DataRg).AutoFill Destination:=Range(Tcell), Type:=xlFillDefault
End Sub
Ivan
