Run-time error 1004, App Defined or Object Defined Error

[mtolleson]

Well, I've gotten really far in the last six weeks with VBA in both Access and Excel without asking a question but I can't work this one out (and I know it's going to be simple).

I'm running the code below (cobbled together from various sources on the web) to extract the last row number from each of many spreadsheets and put them (the row numbers) in column A of a tally spreadsheet. I'm getting "Run-time error 1004, App Defined or Object Defined Error" on the "Offset" line.

Thanks in advance for any help you can offer. (And I apologize for the awkward formatting of the code but I can't really manipulate it in this forum editor.)

Mark

Sub ExtractSORData()

Dim myFile As String, myCurrFile As String, LastRow As Integer
myCurrFile = ThisWorkbook.Name
myFile = Dir("G:\CCM\SORS\2010 Spring Summer\2010 Spring FT\*.xls")

Do Until myFile = ""

Workbooks.Open "G:\CCM\SORS\2010 Spring Summer\2010 Spring FT\" & myFile
Worksheets("scan data").Activate
Range("A75").End(xlUp).Select
LastRow = ActiveCell.Row
Workbooks(myCurrFile).Worksheets("Sheet1").Range("A1").End(xlDown).Offset(1, 0) = LastRow
Workbooks(myFile).Close savechanges:=False
myFile = Dir
Loop

End Sub

 

[xenou]

Hi,
Welcome to the board.

Your error would be an expected one if the worksheet had nothing in it, or had only a value in Cell A1. In those cases End(xlDown) will run to the last row, and you can't offset past the last row.

So this is probably be a good candidate for the reverse movement which is an excellent way to get the last row in a column - start at the bottom and end(xlUp) to the last row:

Workbooks(myCurrFile).Worksheets("Sheet1") _
    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = LastRow

Hope that helps,
ξ

 

[xenou]

Note: regarding posting your code, enclose the code in CODE tags:

[CODE]Your Code Typed Here[/CODE]

If you set your your message editor interface to standard or enhanced, you can also use the # button to highlight your code and apply the code tags -- USER CP | Edit Options | Miscellaneous Options (at the bottom)

ξ

 

[mtolleson]

Wow, that was quick! And I bet that'll do it. I'll report back as soon as I give it a try tomorrow morning.

Thanks,

Mark

 

[xenou]

No problem.

BTW, strictly speaking, this will still return the wrong last row if the worksheet is empty...

So to be more sure against this possibility (a quick rewrite in keeping with your original idea):

Dim x As Long
Dim LastRow As Long

x = Workbooks(myCurrFile).Worksheets("Sheet1") _
    .Range("A" & Rows.Count).End(xlUp).Row

If x > 1 Then
        LastRow = x + 1
Else

    '//We stopped in Row 1.  Is there a value in A1?
    If Len( _
        Workbooks(myCurrFile).Worksheets("Sheet1") _
        .Range("A" & Rows.Count).End(xlUp).Value _
        ) > 0 Then

        LastRow = 2
    Else

        LastRow = 1
    End If

End If

 

[mtolleson]

Woo-hoo! The first option worked like a charm and got the job done, but I'm going to try the longer one too because, well, I can't not.

Thanks for your help.

Mark T.