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
--
russ

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

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.




"Russ" wrote:

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
--
russ

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


"Russ" wrote in message
...
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
--
russ

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




--
Regards,

OssieMac


"Russ" wrote:

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
--
russ

Thanks to all of you. I now have the solution.
--
russ


"Russ" wrote:

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
--
russ