0

Please can you help with the below; I have an increasing list of staff listed in A1, and I want my formulas in E1, F1, G1, etc. to be autofilled and not static like I have now.

I have the below but its throwing up the error: Method 'Range' of object '_Global' failed

My vba code;

Sub AutoFill()

    Dim RowCount As Variant

    RowCount = Range(("A1"), Range("A1").End(xlDown)).Rows.Count
    Range("E1").Select ' Default formula's in E1
    Selection.AutoFill Destination:=Range(RowCount - 1), Type:=xlFillDefault

End Sub

What am I missing, i'm stumped and have been since yesterday...

Thanks in Advance.

Rilude
  • 1
  • 1
  • 1
  • 1
  • Possible duplicate of [Run-time error '1004' : Method 'Range' of object'\_Global' failed](https://stackoverflow.com/questions/12174723/run-time-error-1004-method-range-of-object-global-failed) – FunThomas Dec 13 '18 at 11:59
  • `Range(RowCount - 1)` this is just a number, not a range, need something like `Range("A1:A" & RowCount - 1)` – Nathan_Sav Dec 13 '18 at 12:09

1 Answers1

1

The Range.FillDown method method may be easier to code.

Sub DynAutoFill()

    Dim lastRow As long

    lastRow = cells(rows.count, "A").end(xlup).row  'should -1 be added here?
    Range(cells(1, "E"), cells(lastRow, "E")).filldown

End Sub

FillDown can easily handle multiple columns.

Sub DynAutoFill()

    Dim lastRow As long

    lastRow = cells(rows.count, "A").end(xlup).row  'should -1 be added here?
    Range(cells(1, "E"), cells(lastRow, "G")).filldown

End Sub

Your own code needs the entire range as the destination.

Sub DynAutoFill()

    Dim RowCount As Variant

    RowCount = Range(("A1"), Range("A1").End(xlDown)).Rows.Count
    Range("E1").AutoFill Destination:=Range(cells(1, "E", cells(RowCount - 1, "E")), Type:=xlFillDefault

End Sub

I wouldn't use a reserved word for the name of the sub procedure.

  • user10781941 - you're amazing! I have one question, my manager has now got data and a 'sexy border' in the first few rows. Can I start the code from say A7 instead of A1? – Rilude Dec 13 '18 at 14:08
  • Should be as simple as `Range(cells(7, "E"), cells(lastRow, "E")).filldown` with your seeded formula in E7. –  Dec 13 '18 at 14:17