range value with variable not working?

[James]

Hi,
I am trying to copy a formula down a few cells in a column.
I have the following code:

Sheets("ReportOutput").Range("F4").Copy
For i = 1 To NumRows
Sheets("ReportOutput").Range((i + 3), 6).PasteSpecial xlPasteFormulas
Next i

the cell F4 already has the formula in it. When it runs, I get

Runtime error 1004, Application defined or object-defined error

If I go to the debug screen, its the paste line that is highlighted. If I
replace the (i+3),6 with a normal ref. like "F6", it works... but I need to
be able to loop through as I won't know beforehand how many rows of data
there will be? NumRows is a variable with the no. of rows returned from a
query - this works ok as prior to this bit of code I have another loop
exactly the same to set some cell formats (which works).

It seems I can use the (i+3,6) style reference on cells okay, but the
pastespecial bit only works on a range, which doesnt like that method, so a
bit of a catch 22.
Help!
James.

 

[Tom Ogilvy]

Change Range to Cells

Sheets("ReportOutput").Range("F4").Copy
For i = 1 To NumRows
Sheets("ReportOutput").Cells((i + 3), 6).PasteSpecial xlPasteFormulas
Next i

 

[Bernie Deitrick]

James,

No need to loop:

With Sheets("ReportOutput").Range("F4")
.Copy
.Resize(NumRows, 1).PasteSpecial xlPasteFormulas
End With

HTH,
Bernie
MS Excel MVP

 

[James]

Change Range to Cells

Sheets("ReportOutput").Range("F4").Copy
For i = 1 To NumRows
Sheets("ReportOutput").Cells((i + 3), 6).PasteSpecial xlPasteFormulas
Next i

Thanks,
I then get error 1004, PasteSpecial of Range class failed.


I think becuase the pastespecial method can only be applied to a range?

 

[James]

James,

No need to loop:

With Sheets("ReportOutput").Range("F4")
.Copy
.Resize(NumRows, 1).PasteSpecial xlPasteFormulas
End With

That seems to work a treat.
Thanks,
James

 

[Tom Ogilvy]

True, pasting to a range is one form of PasteSpecial. But that is a range:

From the immediate window.
i = 1
? typename(Sheets("ReportOutput").Cells((i + 3), 6))
Range
? Sheets("ReportOutput").Cells((i + 3), 6).Address
$F$4

This ran fine for me:

Sub AAA()
NumRows = 10
Sheets("ReportOutput").Range("F4").Copy
For i = 1 To NumRows
Sheets("ReportOutput").Cells((i + 3), 6).PasteSpecial xlPasteFormulas
Next i

End Sub

Of course Bernie is correct that you don't need to loop.