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

  • 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.


    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.

  • Hello and Welcome to the Forum

    You could test following

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • 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:

  • Glad to hear this could help :wink:

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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • 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.

    With ActiveSheet
            lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
        End With
  • In that case you don't need the first With Statement. In fact you probably don't need AutoFill, try this

    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
  • '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.

    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
  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!