xlDown going further than data

[Astello]

I have a macro in excel that fills a formula in the first cell in a
column, then copies that formula and fills it into the rest of the
column. There is placeholder data in this column, so that the formula
will only be copied to a certain amount of cells. My code looks like
this:

Range("P2").Select
ActiveCell.FormulaR1C1 = "=Miles(RC[-10],RC[-5])"
Range("P2").Select
Selection.Copy
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Range("P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Now the code works fine, but for some reason it pastes the formula well
past the data that is in the column (ex. if data is in rows 1-42, the
formula gets copied to rows 1-112) which gives me a bunch of "#N/A"s,
which causes problems when I try to sum up these values. Any idea why
this is happening? I thought putting in "xlDown" was supposed to
prevent this problem...

 

[Don Guillett]

try

Sub copyfomrula()
lr = Cells(Rows.Count, "P").End(xlUp).Row
Range("P2").FormulaR1C1 = "=Miles(RC[-10],RC[-5])"
Range("p2" & lr).FillDown
End Sub

to convert to values
Sub copyfomrula()
lr = Cells(Rows.Count, "P").End(xlUp).Row
Range("P2").FormulaR1C1 = "=Miles(RC[-10],RC[-5])"
With Range("p2" & lr)
..FillDown
..Value = .Value
End With
End Sub

 

[Jon Peltier]

If you have a formula that returns "", that is not a blank cell, it contains
a formula. End(xlDirection) goes until it hits a blank, or starting from a
blank, it goes until it hits a non-blank.

- Jon