Run time error '1004': AutoFill method of Range class failed

Damion1986 · Nov 1st 2019

Run time error '1004': AutoFill method of Range class failed

Hi guys,

I am completely new to VBA and currently started an apprenticeship this week, a lot of it is to do with coding VBA and C#.Net. I have been looking at some coding which has an issue somewhere along the lines, I have been trying to figure this one out all day and just cant solve this.

[SIZE=14px]The reason for the code:[/SIZE]

Currently trying to make this code work for an excel sheet to populate a data set which can vary from having 1000 rows to 1 row. The issue arises when i run the code and any data that only has 1 row seems to get this message and doesn't continue successfully. However, if there are more than 1 row of data, the issue does not exist and it continues fine.

What am I missing here? Any help would be highly appreciated as I am a complete newbie to coding.

The bug seems to be highlighting this area of the code marked in red.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Code

With ActiveSheet


lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
End With
Range("I1").Formula = "= H1*100"
Range("I1").AutoFill Destination:=Range("I1:I" & lastrow)


Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Range("I8").Select
ActiveWindow.SmallScroll Down:=-12


With ActiveSheet


lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
End With
Range("I1").Formula = "= H1*2"
Range("I1").AutoFill Destination:=Range("I1:I" & lastrow)


Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Display More

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I have tried to add an IF statement to the "lastrow" condition which didnt work for me. I have a trust book called the Excel Macros for dummies but haven't been successful with that either.

Many thanks in advance for your help.

**Carim** · Nov 1st 2019

Hello and Welcome to the Forum

You could test following

Code

Sub TestCopy()
Dim lastrow As Long
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With
    Range("I1").Formula = "= H1*100"
    If lastrow > 1 Then Range("I1").AutoFill Destination:=Range("I1:I" & lastrow)
    Range("I1:I" & lastrow).Value = Range("I1:I" & lastrow).Value
    Columns("H:H").Delete Shift:=xlToLeft
    Range("I1").Formula = "= H1*2"
    If lastrow > 1 Then Range("I1").AutoFill Destination:=Range("I1:I" & lastrow)
    Range("I1:I" & lastrow).Value = Range("I1:I" & lastrow).Value
End Sub

Display More

Hope this will help

Damion1986 · Nov 4th 2019

Thank you for the advice, I had a feeling there should have been an IF statement in regards to last row with a sub routine command but to control this. I was unsure of the code or location to incorporate such command. I will test this soon and let you know how I got on.

Again, many thanks for the help :smile:

**Carim** · Nov 4th 2019

Glad to hear this could help :wink:

Thanks for your Thanks ...AND for the Like ...:smile:

**royUK** · Nov 4th 2019

This With... End With Statement is unnecessary. I am assuming that the code is run by a button on the sheet, other wise the rest of the code may not work as expected.

Code

With ActiveSheet
        lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

Damion1986 · Nov 4th 2019

Hi, yes the macro is run by a button on the worksheet.

**royUK** · Nov 4th 2019

In that case you don't need the first With Statement. In fact you probably don't need AutoFill, try this

Code

Option Explicit


Sub TestCopy()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "H").End(xlUp).Row
    Range("I1:I" & LastRow).Formula = "= H1*100"
    Range("I1:I" & LastRow).Value = Range("I1:I" & LastRow).Value
End Sub

**royUK** · Nov 4th 2019

'm not sure what you are doing exactly, but I made a dummy workbook and this code works for me. It multiplies entries in H by 100 and in I by 2, then deletes column H.

It works on one line of data or many.

Code

Option ExplicitSub TestCopy()    Dim lastrow As Long    lastrow = Cells(Rows.Count, "H").End(xlUp).Row    Range("I1:I" & lastrow).Formula = "= H1*100"     Range("J1:J" & lastrow).Formula = "= I1*2"    Range("I1:j" & lastrow).Value = Range("I1:j" & lastrow).Value    Columns(8).EntireColumn.DeleteEnd Sub

Damion1986 · Nov 5th 2019

Good news and bad news, Carim the coding you suggested didn't work for this particular application. But the help was greatly appreciated, I think it may have been missing a <2 factor for last row, to result in only one row being selected. However, this has also now given me the logical thinking behind why you gave that code.

RoyUK, both of the codes worked perfectly. The last code simplified the VB statement with elegance. I normally record macros and learn the codes from there. In this instance, the auto-fill was indicating the drag of the cells to be replicating a results in that column. However, in one cell selection this could not be recorded, which is why it needed to be written as a range statement.

I am slowly starting to understand the logic behind VB and I appreciate all your help with this Carim & RoyUK. :-D:-D:-D

Run time error '1004': AutoFill method of Range class failed

Participate now!

Share

Users Viewing This Thread