Autofill method of range class failed error

Posted on 2011-10-27
Last Modified: 2012-05-12
Private Function FormulaFiller(idata As String, iStart As String, iend As String)
    Dim rgCopy As Range, rgDelete As Range, rgFill As Range, rgClear As Range
    Set rgCopy = Evaluate(iStart & ":" & iend)     'Formulas in these cells will be copied down
    Set rgFill = Evaluate(idata)        'First piece of raw data
    Set rgFill = Range(rgFill, rgFill.Worksheet.Cells(Rows.count, rgFill.Column).End(xlUp))  'All data in that column
    Set rgFill = rgCopy.Resize(rgFill.Rows.count)       'Fill formulas down in this range
    Set rgDelete = rgCopy.Offset(1, 0).Resize(rgCopy.Worksheet.UsedRange.Rows.count)
    rgFill.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(237, 237, 4)
    rgCopy.AutoFill rgFill, xlFillCopy
    Application.CutCopyMode = False

End Function

Open in new window

The above function get called by:

Call FormulaFiller("A3", "N3", "Q3")


The problem I am having is that, this works if there are multiple lines of raw data (from say A3:M20) and I am trying to pull down formulas from rows N3:Q3

But when I have a single line of raw data only populating from A3 to M3, the function gives me ‘Autofill method of range class failed’ error.

Is there a way to skip the function if only row A3:M3 is populated?

Question by:Shanan212
    LVL 33

    Accepted Solution

    Do you mean if rgFill does not have more than 1 row then it fails?

    You could just check for that.
    If rfFill.Rows.Count > 1 Then
    ' do autofill
    ' don't autofill
    End If

    Open in new window

    By the way, this seems quite a complicated way to acheive what I think you want to do.
    LVL 13

    Author Closing Comment

    Thanks! Wasnt thinking of that :/

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now