+ Reply to Thread
Results 1 to 2 of 2

How to copy formula from one cell to a range?

  1. #1
    Jason Weiss
    Guest

    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. #2
    Toppers
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1