Runtime 1004 - AutoFill method of range class failed - Formula fix?

V

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
1,027
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I have used this formula several times and it has worked perfectly, until now.

When I tried this formula... (from within a macro)

Code: 
Selection.AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)

...using this data here....

<TABLE style="WIDTH: 323pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=431 border=0><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 226pt; mso-width-source: userset; mso-width-alt: 11008" width=301><TBODY><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 23pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 36pt; BACKGROUND-COLOR: maroon" width=31 height=48>#</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=99>Customer Number</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 226pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=301>Customer Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>1</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=99>ABC0123</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 226pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=301>John Smith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>2</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 74pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=99>ABC0124</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 226pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=301>Mary Jones</TD></TR></TBODY></TABLE>




I get this error here...

Runtime 1004 - AutoFill method of range class failed
Click to expand...



So, it works fine with 3 or more data rows, but it seems to bug when there are less than 3 data rows (not including the title row)

Can anyone help me with this one?

Thanks much
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
This worked for me

Code: 
Sub test()
Range("A2").AutoFill Destination:=Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row), Type:=xlFillSeries
End Sub
 
Upvote 0
Or this...
Code: 
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    If Lastrow > 2 Then
        Range("A2").AutoFill Destination:=Range("A2:A" & Lastrow)
    End If
 
Upvote 0
Thanks for all the input guys.

Selection was Column A, minus the title row.

AlphaFrog gets the prize though, problem solved, that solution worked perfectly!

Thanks much :biggrin:
 
Upvote 0
Alpha Frog,

Sorry, I was a bit too quick to say this was perfect. It did eliminate the error, but I want the numbers in column A to count up sequentially like this...

<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=382 border=0><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><TBODY><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 23pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 36pt; BACKGROUND-COLOR: maroon" width=31 height=48>#</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=151>Customer Number</TD><TD class=xl65 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=200>Customer Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>1</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=151>ABC1234</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=200>John Smith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>2</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=151>ABC1235</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=200>Mary Jones</TD></TR></TBODY></TABLE>






The code you provided leaves them all as "1" like this...

<TABLE style="WIDTH: 286pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=382 border=0><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><TBODY><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD class=xl70 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 23pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 36pt; BACKGROUND-COLOR: maroon" width=31 height=48>#</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=151>Customer Number</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: maroon" width=200>Customer Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>1</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=151>ABC1234</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=200>John Smith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>1</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: black; WIDTH: 113pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=151>ABC1235</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black; BORDER-LEFT: #ece9d8; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=200>Mary Jones</TD></TR></TBODY></TABLE>

Can the code be modified to accommodate this?

Thanks much
 
Upvote 0
Code: 
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    If Lastrow > 1 Then
        Range("A2").Value = 1
        Range("A2:A" & Lastrow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
    End If
 
Upvote 0
Alphafrog,

Purrrfection, twice over! This time for real. Exactly what I needed.


Thanks again for all your help!

:biggrin: :LOL::LOL: :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
832
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
R
  • Question
Random
Replies
1
Views
446
Fluff
Fluff
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top