Forum Discussion

Sonal2309's avatar
Sonal2309
Copper Contributor
Jan 09, 2020

Auto Fill Error when output data are less than 1

Hi,

 

I have been trying to use below AutoFill code, but I am getting an error when data is less than 1. Is there a way for get away with this error:

 

Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("E2").Select
Application.CutCopyMode = False

Macros and VBA

9 Replies

  • peanut's avatar
    peanut
    Copper Contributor
    Jan 14, 2020

    Hi Sonal2309 

    The trouble is not with the code that you posted but in the previous line:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Sheet3,2,0)"

     

    This line creates an error in the field D2. Then you tell Excel to fill-down the error which Excel refuses to do.

    To check it yourself, while in Excel, press ALT+F11 and it will open VBA editor. Then use F8 to go line by line and watch you spreadsheet as you keep pressing F8.

    I would correct your code, but I don't know how exactly your VLOOKUP works.

    For start, look for VBA examples of VLOOKUP.

     

    If you give me more details, maybe I can fix it for you.

    • Sonal2309's avatar
      Sonal2309
      Copper Contributor
      Jan 14, 2020

      peanut Thank you but I know that the error is not in Vlookup and it is in Autofill (Please check the subject line of my query carefully). Although I used record macro to make the coding I know how to edit & so I was seeking help for it.

      I have used the IF condition and resolved the error.

  • PascalKTeam's avatar
    PascalKTeam
    Iron Contributor
    Jan 14, 2020
    Hi. Thanks for the file.
    I assume you have created this macro with the macro recorder, there are a lot of steps in it which are not needed.
    Since I sill don't know what you are actually trying to do, I can't help you.

    Please explain what the macro is supposed to do
    • Sonal2309's avatar
      Sonal2309
      Copper Contributor
      Jan 14, 2020

      PascalKTeam  Hi Pascal, There are some sensitive things for which I can't disclose the data. I gave you a better portion of coding already. What I was trying to reach was to avoid the Autofill data error when the data is less than 1.

       

      However, I have achieved the same by using IF condition. I know there are coding made by recording the macro but what is the harm in it.

       

Resources