+ Reply to Thread
Results 1 to 6 of 6

VBA code to Autofill one cell to many rows below where row count will change

  1. #1
    TrainingGoddess
    Guest

    VBA code to Autofill one cell to many rows below where row count will change

    Sub AutoFillDateLookups()
    Dim DataRange As Range
    Dim CopyRange As Range
    Dim FillRange As Range
    Set DataRange = Range("ClassCountRevenue")
    Set CopyRange = Range("F2")
    Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count,
    0).Select
    CopyRange.AutoFill Destination:=Range(FillRange)
    End Sub

    I am attempting to AutoFill a range of values from one cell (F2) to a
    range of cells below where the number of rows will vary from month to
    month.

    In the example above, I have set the datarange to capture the total
    rows/columns in the data table. I set the copy range to the starting
    cell containing my formula. I can't seem to get the fill range to be
    one row down and then resize to the total count of rows in the data
    table.

    Any help would be much appreciated.

    Thanks!
    Kimberly


  2. #2
    Rowan
    Guest

    RE: VBA code to Autofill one cell to many rows below where row count w

    Kimberley

    I approach this slightly differently. First find you last used row and then
    insert the required formula into all cells in the column base on this last
    row. This means you do not need to use the Autofill. e.g.

    Dim endRow As Long
    endRow = Range("E" & Rows.Count).End(xlUp).Row
    Range(Cells(2, 6), Cells(endRow, 6)).FormulaR1C1 _
    = "=RC[-2]+RC[-3]"

    Hope this helps
    Rowan

    "TrainingGoddess" wrote:

    > Sub AutoFillDateLookups()
    > Dim DataRange As Range
    > Dim CopyRange As Range
    > Dim FillRange As Range
    > Set DataRange = Range("ClassCountRevenue")
    > Set CopyRange = Range("F2")
    > Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count,
    > 0).Select
    > CopyRange.AutoFill Destination:=Range(FillRange)
    > End Sub
    >
    > I am attempting to AutoFill a range of values from one cell (F2) to a
    > range of cells below where the number of rows will vary from month to
    > month.
    >
    > In the example above, I have set the datarange to capture the total
    > rows/columns in the data table. I set the copy range to the starting
    > cell containing my formula. I can't seem to get the fill range to be
    > one row down and then resize to the total count of rows in the data
    > table.
    >
    > Any help would be much appreciated.
    >
    > Thanks!
    > Kimberly
    >
    >


  3. #3
    Rowan
    Guest

    RE: VBA code to Autofill one cell to many rows below where row count w

    Otherwise if the formula is not coming from your VBA code to start with you
    can use yuor macro as follows:

    Sub AutoFillDateLookups()
    Dim DataRange As Range
    Dim CopyRange As Range
    Dim FillRange As Range
    Set DataRange = Range("ClassCountRevenue")
    Set CopyRange = Range("F2")
    CopyRange.AutoFill Destination:=Range(Cells(2, 6), _
    Cells(Range("E" & Rows.Count).End(xlUp).Row, 6))

    End Sub

    Regards
    Rowan

    "TrainingGoddess" wrote:

    > Sub AutoFillDateLookups()
    > Dim DataRange As Range
    > Dim CopyRange As Range
    > Dim FillRange As Range
    > Set DataRange = Range("ClassCountRevenue")
    > Set CopyRange = Range("F2")
    > Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count,
    > 0).Select
    > CopyRange.AutoFill Destination:=Range(FillRange)
    > End Sub
    >
    > I am attempting to AutoFill a range of values from one cell (F2) to a
    > range of cells below where the number of rows will vary from month to
    > month.
    >
    > In the example above, I have set the datarange to capture the total
    > rows/columns in the data table. I set the copy range to the starting
    > cell containing my formula. I can't seem to get the fill range to be
    > one row down and then resize to the total count of rows in the data
    > table.
    >
    > Any help would be much appreciated.
    >
    > Thanks!
    > Kimberly
    >
    >


  4. #4
    Jim Cone
    Guest

    Re: VBA code to Autofill one cell to many rows below where row count will change

    Kimberly,

    Several things ...
    Resize: you must omit or specify the number of columns, 0 is not valid.
    AutoFill: the destination range must include the source range.
    FillRange: It is already a range object, so Range(FillRange) is invalid.

    The following will work if DataRange is a valid range object...
    '----------------------------------------
    Sub AutoFillDateLookups()
    Dim DataRange As Range
    Dim CopyRange As Range
    Dim FillRange As Range

    Set DataRange = Range("ClassCountRevenue")
    Set CopyRange = Range("F2")
    Set FillRange = CopyRange.Resize(DataRange.Rows.Count + 1, 1)

    CopyRange.AutoFill Destination:=FillRange
    End Sub
    '-------------------------------------------

    Regards,
    Jim Cone
    San Francisco, USA



    "TrainingGoddess" <kmills_ata@yahoo.com> wrote in message news:1111552571.111081.178700@z14g2000cwz.googlegroups.com...
    > Sub AutoFillDateLookups()
    > Dim DataRange As Range
    > Dim CopyRange As Range
    > Dim FillRange As Range
    > Set DataRange = Range("ClassCountRevenue")
    > Set CopyRange = Range("F2")
    > Set FillRange = CopyRange.Offset(1, 0).Resize(DataRange.Rows.Count, 0).Select
    > CopyRange.AutoFill Destination:=Range(FillRange)
    > End Sub
    >
    > I am attempting to AutoFill a range of values from one cell (F2) to a
    > range of cells below where the number of rows will vary from month to
    > month.
    >
    > In the example above, I have set the datarange to capture the total
    > rows/columns in the data table. I set the copy range to the starting
    > cell containing my formula. I can't seem to get the fill range to be
    > one row down and then resize to the total count of rows in the data
    > table.
    >
    > Any help would be much appreciated.
    >
    > Thanks!
    > Kimberly
    >


  5. #5
    Kimberly Mills
    Guest

    RE: VBA code to Autofill one cell to many rows below where row count w


    Thanks for your help!


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

  6. #6
    Kimberly Mills
    Guest

    Re: VBA code to Autofill one cell to many rows below where row count will change


    Thanks for your help!


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

+ 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