Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy formula from one cell to a range?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copy formula from one cell to a range?
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 "Jason Weiss" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
copy range and paste into every 3rd cell of new range | New Users to Excel | |||
range of Formula copy but not changing the content | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
Copy Excel Spreadsheet formula to a range via VB6 | Excel Programming |