PDA

View Full Version : Copy formula down the column



torgerjl
08-14-2008, 10:08 AM
I need to copy a formula, just as it is from one cell to another; I don't know how to make it continual down the page. For example, I copy the formula from A1 to A2; I do not know how to continue the function to B1 to B2 and so and so forth.

This is how it looks for one copy paste now
Sub Copy()
Range("BC9").Select
ActiveCell.FormulaR1C1 = "=SUM('Source Data'!R[24]C[-33])"
Range("T9").Select
ActiveCell.FormulaR1C1 = "=SUM('Source Data'!R[24]C[2])"
Range("AV9").Select
End Sub

Bob Phillips
08-14-2008, 11:37 AM
You talk about A1, A2, B1, B2 and the VBA is nothing to do with any of those cells. I am confused as to what you want to do.

torgerjl
08-14-2008, 11:40 AM
Sorry. I want to copy one result from one cell to another and maintain the formula; if I select all the cells I need to copy (eg, B1 to B219) and paste them into the range I need them to be in (eg, A1 to A219) I lose all the formulas and some formatting. I can not use the Paste Special/Values Only function because I need the formulas to remain. Sometimes the copy paste funtion works and other times I get the #REF! error; if I click into the cell and copy the formula one by one I have no issues, but I'm sure you can see by the cell range why I don't want to do it that way. I would like to write a macro so I don't have to do this every time its needed...
Do that help?

jproffer
08-14-2008, 12:33 PM
Sounds like it's making the formula relative to the cell it's in.
I.E.: In C1 is the formula =A1+B1. If you drag it down as written, the formula in cell C2 will be =A2+B2, C3 will be =A3+B3, and so on. If you drag it to the right, into cell D1 (or 2 or 3) you will have =B1+C1 (or 2 or 3, respectively).

Is that what you wanted, or no?

To "lock" the formula as written, simply put a "$" in front of either or both of the cell reference characters.

I.E.: In C1 is the formula =$A$1+$B$1. If you drag it down to row 65536 and over to column 256 (IV), it will still be =$A$1+$B$1. Or you can lock only part of the cell reference. For instance, if in C1 is =$A1+$B1. If you drag it left or right, it will remain the same, but if you drag it down it will "follow" the row down as you go.

So in C3 or in IV3, the formula would be =$A3+$B3.

The #REF comes from copying a formula to the left that refers to A1 (or A, anything) it shifts the cell reference off the page, and that naturally brings on an error.

Now, as far as in VBA, I'm just tipping the iceburg myself on what it can do and more importantly HOW in the HE** to make it do it. So I can't help you there.

rajkumar
08-26-2008, 04:23 PM
I think you want to fill a range with formula, is it?
then try this

edit sheet names and cell names to suit requirement
Sub fillFormula()
Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry (could have been done from A1 or B1)
lastRw = Worksheets("Sheet1").Range("C1").End(xlDown).Row
With Worksheets("Sheet1").Range("D1")
.Formula = "=SUM(A1:C1)"
.AutoFill Destination:=Worksheets("Sheet1") _
.Range("D1:D" & lastRw&)
End With
End Sub


Raj

:hi: