AutoFill method of Range class failed

[Geshwinn]

Hi guys,

This is the code:

Sub calcWeb()
Dim FinalRow As Long
Dim FinalColumn As Long


With Sheets("Web")
FinalRow = .Cells(Rows.Count, 3).End(xlUp).Row
FinalColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "OK/ERROR"
.Cells(2, 1).FormulaR1C1 = "=IF((COUNTBLANK(RC[1])+COUNTBLANK(RC[9]:RC[21]))=0, 0, 1)"
.Cells(2, 1).AutoFill Destination:=Range("A2:A" & FinalRow)

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "Empty Attributes"
.Cells(2, 1).FormulaR1C1 = "=COUNTBLANK(RC[2])+COUNTBLANK(RC[10]:RC[22])"
.Cells(2, 1).AutoFill Destination:=Range("A2:A" & FinalRow)

End With
Call calcApp
End Sub

Sub calcApp()
Dim FinalRow As Long
Dim FinalColumn As Long

With Sheets("Application")
FinalRow = .Cells(Rows.Count, 3).End(xlUp).Row
FinalColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "OK/ERROR"
.Cells(2, 1).FormulaR1C1 = "=IF(COUNTBLANK(RC[8]:RC[23])=0, 0, 1)"
[COLOR="Red"].Cells(2, 1).AutoFill Destination:=Range("A2:A" & FinalRow)[/COLOR]

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "Empty Attributes"
.Cells(2, 1).FormulaR1C1 = "=COUNTBLANK(RC[9]:RC[24])"
.Cells(2, 1).AutoFill Destination:=Range("A2:A" & FinalRow)

End With
'Call calcDatabase
End Sub

When I run it it stops at this row and says "autofill method of range class failed" but when I run the macros separately, one by one, everything works perfectly.

What could be wrong here?

 

[schielrn]

I think you need to put . dots in front of range because without them it would be working on the activesheet which may not be the same as the sheet you are trying to autofill:

Sub calcWeb()
Dim FinalRow As Long
Dim FinalColumn As Long


With Sheets("Web")
FinalRow = .Cells(Rows.Count, 3).End(xlUp).Row
FinalColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "OK/ERROR"
.Cells(2, 1).FormulaR1C1 = "=IF((COUNTBLANK(RC[1])+COUNTBLANK(RC[9]:RC[21]))=0, 0, 1)"
.Cells(2, 1).AutoFill Destination:=.Range("A2:A" & FinalRow)

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "Empty Attributes"
.Cells(2, 1).FormulaR1C1 = "=COUNTBLANK(RC[2])+COUNTBLANK(RC[10]:RC[22])"
.Cells(2, 1).AutoFill Destination:=.Range("A2:A" & FinalRow)

End With
Call calcApp
End Sub

Sub calcApp()
Dim FinalRow As Long
Dim FinalColumn As Long

With Sheets("Application")
FinalRow = .Cells(Rows.Count, 3).End(xlUp).Row
FinalColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "OK/ERROR"
.Cells(2, 1).FormulaR1C1 = "=IF(COUNTBLANK(RC[8]:RC[23])=0, 0, 1)"
.Cells(2, 1).AutoFill Destination:=.Range("A2:A" & FinalRow)

.Cells(1, 1).EntireColumn.Insert
.Cells(1, 1).Value = "Empty Attributes"
.Cells(2, 1).FormulaR1C1 = "=COUNTBLANK(RC[9]:RC[24])"
.Cells(2, 1).AutoFill Destination:=.Range("A2:A" & FinalRow)

End With
'Call calcDatabase
End Sub

 

[Geshwinn]

Worked great!

Thanks, schielrn!

 

[schielrn]

Glad it worked, it is sometimes hard to remember all the little dots and so forth.