How to copy formula from one cell to a range?

[Jason Weiss]

Hi,

I'm having a devil of a time copying a formula from one cell to a
variable range of cells using VBA. The code I came up with is the
following:

Dim ReferenceRow As Integer
Dim ReferenceCol As Integer

....

With Worksheets(ShtName)
.Range("A1").Copy
.Range(Cells(ReferenceRow, 5), Cells(ReferenceRow, _
ReferenceCol)).PasteSpecial Paste:=xlPasteFormulas
End With

It throws "run-time error 1004: Application-defined or object-defined
error" on the PasteSpecial command when I run it, and I can't figure out
why.

The issue is that I need the formula to copy over using relative
addressing (e.g., the cells referenced in the formula in A1 would need
to change according to the new cells), so I can't just copy and paste
the formula directly.

Any ideas?

Thanks...

....Jay

 

[Guest]

Jason,
Worked OK for me but you will get your error message if the
values of ReferenceRow and/or ReferenceCol are invalid e.g 0.

HTH