Run time error '9'

[BEEJAY]

The following gives this error, with "subscript out of range" message
When I select debug, it hi-lites the 3rd line.
As far as I can see, this process is a direct copy from elsewhere in this ng.
what am I missing?

' Copy formulas in G2 and I2 down thru to last used Row

Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G3:G" & lastrow)

lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("I2").AutoFill Destination:=Range("I3:I" & lastrow)

Thanks in advance.

 

[Naveen]

Try this ...

===========================================
Sub Naveen()
Dim lastrow As Long
lastrow = ActiveCell.SpecialCells(xlLastCell).Row
Range("G2:I2").AutoFill Destination:=Range("G2:I" & lastrow)
End Sub
===========================================

*** Please do rate ***

 

[Tom Ogilvy]

Generally subscript out of Range would refer to the argument to Worksheets.
This is the sheet name (tab name): "Sheet1". You would get that error if
you did not have a "Sheet1" in the currently active workbook.

 

[Naveen]

Try this also ...

=============================================
Sub Naveen()
Dim lastrow As Long
lastrow = Range("A1").SpecialCells(xlLastCell).Row
Range("G2:I2").AutoFill Destination:=Range("G2:I" & lastrow)
End Sub
=============================================

*** Please do rate ***

 

[Tom Ogilvy]

Once you get the first error cleared up, you will have problems with your
autofill code. You must include the source range in the destination range.
This is tested and worked fine for me:

Sub ABC()
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells( _
Rows.Count, "A").End(xlUp).Row
Range("G2").AutoFill _
Destination:=Range("G2:G" & lastrow)
Range("I2").AutoFill _
Destination:=Range("I2:I" & lastrow)
End Sub


since you used the same location to determine the lastrow and your code
doesn't change that, you don't need to do it twice.

 

[Tom Ogilvy]

Naveen,

Since you asked to be rated, here are a few observations:

the specialCells(xlLastCell) command returns the same information regardless
of which rng is used to anchor the command.

xlLastCell is not designed for the purpose you use it for. It is designed
to tell you which is the lower right corner of the rectangular range of cells
that excel is keeping detailed information on - the used range so to speak.
the remainder of the spreadsheet is virtual. The filled area is definitely a
subset of this used range, but may not be equal to the used range. If the
sheet has had more usage and then cells just deleted, the used range is not
necessarily reset. So UsedRange and xlLastCell may be unreliable for the
purpose that the OP appears to intend.

Also, there was no indication in the post that this is even what the OP
wanted. Column A may not be filled even equal with the row that contains the
last filled row in the sheet. (the OP specifically looked to column A as the
location for determination and then even possibly on another sheet - see the
next paragraph).

Also, the OP did not qualify the ranges to fill with a sheet name. the
intent could be to get the extent of fill from a different sheet( sheet1) and
fill the active sheet using that value. If so, your code would not meet that
intent. (or it could be an oversight on the OPs part and this isn't an
issue).

Next, your code fills G, H and I, but the OP was only filling G and I.

Based on the OP's actual situation, your suggestion may work and probably
will, but then again it could be a hidden timebomb waiting to misbehave.

 

[BEEJAY]

Gentlemen:
Thanks for all the input and all the information.

Tom, your absolutely correct. The process that gives me the spread-sheet
also renames the sheet (will be different each time)
1: Each workbook will have only ONE ws
2: Each ws will have a different name
3: All of my code is to manipulate data within that one sheet.

A: How do I reference Sheet 1 by position rather than by name?
I've tried to properly question the existing info in the ng.
Either I'm not asking the question properly, or its not there.

 

[BEEJAY]

A little more experimenting, and I found it.
lastrow = Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Thanks again for all the help