[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 595
  • Last Modified:

Autofill method of range class failed error

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?

1 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.
Shanan212Author Commented:
Thanks! Wasnt thinking of that :/

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now