copying a formula to another cell

R

Russ

This is a trivial question but I have run into a wall trying to find the
answer.
I have a formula in a cell which I want to copy to the cells below with a
simple routine. A sample routine looks like this:

Public Sub test_case()
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = .Cells(1, 1)
Next
End With
End Sub

In cell(1,1) is the sample formula =RC2/RC3. The routine, as it is, copies
the formula down to the 4 cells below. =RC2/RC3 shows in the cells but it is
not a formula that equates to the value. How do I change the code to get it
to be a real formula? Any help will be greatly appreciated
 
C

CurlyDave

Perhaps this would be the way to go

Dim r As Long
Dim s As String
s = "=RC2/RC3 "
Cells(1, 1) = s
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = s
Next
End With
 
J

JLGWhiz

You were not too far off.

Public Sub test_case()
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1).Formula = .Cells(1, 1).Formula
Next
End With
End Sub

However, It copies the formula as absolute reference. i.e. If your formula
is =b1+c1, then each cell in the range will be =b1+c1 and not =b2+c2, =b3+c3,
etc.
 
J

JLGWhiz

I think this might be more what you are looking for.

Sub autFil()
Set SourceRange = Sheets(1).Range("A1")
Set FillRange = Sheets(1).Range("A1:A5")
Range("A1").Formula = "=b1/c1"
SourceRange.Autofill FillRange
End Sub
 
O

OssieMac

Hi Russ,

If you want the formula that is copied to be relative in the other cells
then you need to copy the cell not make the others equal to the original cell.

Public Sub test_case()
'Creates formulas and makes them relative
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(1, 1).Copy .Cells(r, 1)
Next
End With
End Sub


If you want the exact same formula in the other rows then this way.
Public Sub test_case2()
'Copies the exact same formula to other cells
Dim r As Long
With ActiveWorkbook.Sheets(1)
For r = 2 To 5
.Cells(r, 1) = .Cells(1, 1).Formula
Next
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top