+ Reply to Thread
Results 1 to 2 of 2

VBA to copy a formula down a dynamic range

  1. #1
    Registered User
    Join Date
    03-27-2009
    Location
    Boston, United States
    MS-Off Ver
    Excel 2003
    Posts
    12

    Question VBA to copy a formula down a dynamic range

    Hello. I am a newbie to VBA programing, and am looking for a little guidance with some trouble I am having.

    I have a formula I would like to copy down a column until data ends in a different column. I have some code that inserts a formula in cell V4, moves down to V5, inserts a different formula, that I then would like copied down column V until the last entry in column K.

    What I have so far:

    Sub Test()
    Dim LastRow As Long

    LastRow = Range("K65536").Select
    Selection.End(xlUp).Select
    lTargetRows = ActiveCell.Row
    lRow = lTargetRows + 1

    Range("V4").Select
    ActiveCell.FormulaR1C1 = "=RC[-18]"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-18]="""",R[-1]C,RC[-18])"

    'Selection.AutoFill Destination:=Range(V.LastRow), Type:=xlFillDefault

    'Range("V5").AutoFill Destination:=Range("V5:V" & LastRow)

    End Sub


    I was trying to use either of the two commented out lines to copy the formula, but I am getting errors. More specifically, when I uncommend the first of the commented out lines, I get a "run-time error '424': Object Required" error. When I uncomment only the second of those lines, I get a "Run-time error '1004': method 'range' of object'_Global' failed".

    Any help/ best practices would be greatly appreciated!


    Cheers,

    jack

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Help! VBA to copy a formula down a dynamic range

    Jack,

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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