Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Autofill Method of Range Calss Failed 1

Status
Not open for further replies.

YoungTurk

Mechanical
Joined
Jul 16, 2004
Messages
333
Location
US
I have the following code snippet, which causes the subject error:

Sheets("Nx=9.0g Fwd").Activate
Range(Cells(LastItemRow, 11), Cells(LastItemRow, 24)).Select
Selection.AutoFill Destination:=Range(Cells(LastItemRow + 1, 11), Cells(LastItemRow + 1, 24)), Type:=xlFillDefault

The odd part is I've lifted this code from another macro which works just fine:

Range(Cells(FirstAttachRow + i - 2, FirstAttachColumn), Cells(FirstAttachRow + i - 2, 100)).Select
Selection.AutoFill Destination:=Range(Cells(FirstAttachRow + i - 2, FirstAttachColumn), Cells(FirstAttachRow + i - 1, 100)), Type:=xlFillDefault

Suggestions? Explanations of why this works in one place and not another would also be useful as I am a VBA novice.

Thanks,
-Turk
 
The following code worked:

Sheets("Nx=9.0g Fwd").Activate
Range(Cells(LastItemRow + 1, 11), Cells(LastItemRow + 1, 24)).FillDown

I'm still lost as to why the original code generated the error.
 
The range and cell properties as you used them in the sample code rely on default expressions that return excel objects. Occassionally the default object is not what you expect and the code doesn't work. Try the following...
Code:
With Sheets("Nx=9.0g Fwd")
.Activate
.Range(.Cells(LastItemRow, 11), .Cells(LastItemRow, 24)).Select
Selection.AutoFill Destination:=.Range(.Cells(LastItemRow + 1, 11), .Cells(LastItemRow + 1, 24)), Type:=xlFillDefault
End With
The With statement lets you attach a property to a specific object explicitly using the dot operator. When you see .Range it is the same as Sheets("Nx=9.0g Fwd").Range

HTH
 
Didn't seem to solve the problem, but thanks for the info on the with command; quite handy!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top