Initializing Find

[MWE]

I am running Word2000 and Excel2000. Sub A can be called many times from Word. Each time it is called, it figures out what workbook (of several) to open and which sheet for that workbook to explore. The logic that selects the workbook and selects the worksheet works fine. The code to create an Excel object, open the workbook, etc., close the workbook, quit the object, etc., all works fine the first time a given workbook/worksheet are opened and explored. In particular, I determine the last useful row in the target sheet using the standard Find method:
[vba]xlLastRow = xlsheet.Cells.Find("*", xlsheet.Cells(1), _
xlFormulas, xlWhole, xlByRows, xlPrevious).Row[/vba] the value in xlLastRow the first time Sub A is called is correct. But on subsequent calls an error occurs: err = 91; Object variable or With block variable not set. This normally occurs with the above method when the worksheet is blank (not true in this case). I believe the problem is the all-too-common problem with getting the Find method to "initialize".

Any help would be appreciated.

[johnske]

Hi Mark, try UserForm_Activate instead of UserForm_Initialize and see how it goes

[MWE]

Hi Mark, try UserForm_Activate instead of UserForm_Initialize and see how it goes

thanks for the prompt reply. I must not have been clear re the problem; but I am not using UserForms here.

[malik641]

Are you saying that you check the SAME excel sheet twice and get the error? Or just simply calling macro A a second time?

[johnske]

Sorry Mark,

You've lost me here, I don't understand what you mean by this...

... I believe the problem is the all-too-common problem with getting the Find method to "initialize". ...

The following similar example is error-free and works fine every time and for all cases I can think of [vba]Option Explicit
Sub Find_LastRowxlFormulas()
On Error GoTo Finish
'give the last row
MsgBox "Last row is row " & Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlFormulas, _
SearchDirection:=xlPrevious).EntireRow.Row
Exit Sub
Finish:
MsgBox "No formulas or values found"
End Sub[/vba]Regards,
John

[mvidas]

MWE,

That error is probably pertaining to the xlsheet variable, is that being passed to your sub? If so, could it be being set to Nothing at some point in the calling procedure?
You may need to post more of your code for us to help further, but that error will mean one of two things:
1) xlSheet isn't a valid sheet reference the second time around
2) No cell was found, so it can't find a .Row for it

Matt

Also, at least we don't have any wind today, but it still isn't much like spring out there. At least it is getting slightly warmer! Too bad we didn't really have a winter though

[MWE]

MWE,

That error is probably pertaining to the xlsheet variable, is that being passed to your sub? If so, could it be being set to Nothing at some point in the calling procedure?
You may need to post more of your code for us to help further, but that error will mean one of two things:
1) xlSheet isn't a valid sheet reference the second time around
2) No cell was found, so it can't find a .Row for it

Matt

Also, at least we don't have any wind today, but it still isn't much like spring out there. At least it is getting slightly warmer! Too bad we didn't really have a winter though

Thanks for the reply.
xlsheet is defined two lines above the find statement line. The next line prints out xlsheet.name and it prints out the correct sheet name. The relevant code:[vba] Set xlAppl = CreateObject("Excel.Application")
Set xlBook = xlAppl.Workbooks.Open(BookFileName)
MsgBox xlBook.Name
For J = 1 To xlBook.Worksheets.Count
Set xlsheet = xlBook.Worksheets(J)
MsgBox xlsheet.Name
xlLastRow = xlsheet.Cells.Find("*", xlsheet.Cells(1), _
xlFormulas, xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then
MsgBox "error code finding last row" & vbCrLf & _
"Error Number:" & vbTab & Err.Number & vbCrLf & _
"Error Desc:" & vbTab & Err.Description
GoTo CleanUp
End If[/vba] at the end of the proc everything is cleaned up, xlbook is quit, etc.

I should clarify that Sub A is called is called multiple times in a loop. The code runs fine the first time Sub A is called, but the second and subsequent times, there is an error in the Find statement.

The # of sheets in the target workbook is 3 in this particular case. The first time the sub is called, the xlLastRow value is correct for each sheet. For subsequent calls, the error occurs for each sheet.

UPDATE: I added a MsgBox display to print out the first few values in xlsheet. They print out correctly. The revised code:
[vba] Set xlAppl = CreateObject("Excel.Application")
Set xlBook = xlAppl.Workbooks.Open(BookFileName)
MsgBox xlBook.Name
For J = 1 To xlBook.Worksheets.Count
Set xlsheet = xlBook.Worksheets(J)
MsgBox xlsheet.Name
MsgBox xlsheet.Cells(1, 1) & vbCrLf & _
xlsheet.Cells(2, 1) & vbCrLf & _
xlsheet.Cells(3, 1)
xlLastRow = xlsheet.Cells.Find("*", xlsheet.Cells(1), _
xlFormulas, xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then
MsgBox "error code finding last row" & vbCrLf & _
"Error Number:" & vbTab & Err.Number & vbCrLf & _
"Error Desc:" & vbTab & Err.Description
GoTo CleanUp
End If[/vba]

[malik641]

Maybe it depends where you call the Sub A for every time after the first. Do you think that, maybe, Sub A is called too soon in the procedure? Not having a sheet to look into in the first place (which would be agreeing with mvidas's #1)? I'm probably totally off here, but....it's late and I'm tired and it's the only thing I could think of.

[MWE]

Maybe it depends where you call the Sub A for every time after the first. Do you think that, maybe, Sub A is called too soon in the procedure? Not having a sheet to look into in the first place (which would be agreeing with mvidas's #1)? I'm probably totally off here, but....it's late and I'm tired and it's the only thing I could think of.

Sub A is called in eactly the same place each time. The workbook is valid, the worksheet is valid, the worksheet has real data in it. That is demonstrated by displaying the first few sheet values before the Find statement.

[malik641]

I can't reproduce the error you get I keep getting "Subscript out of range" for xlLastRow...

Here's my code, where the book I call has 3 sheets, each having the same data in them, and I call the book 3 times and get the same error from the 1st call to the last:

[vba]'Option Explicit

Sub A(BookFileName)
On Error Resume Next
Set xlAppl = CreateObject("Excel.Application")
Set xlBook = xlAppl.Workbooks.Open(BookFileName)
MsgBox xlBook.Name
For j = 1 To xlBook.Worksheets.Count
Set xlsheet = xlBook.Worksheets(j)
MsgBox xlsheet.Name
MsgBox xlsheet.Cells(1, 1) & vbCrLf & _
xlsheet.Cells(2, 1) & vbCrLf & _
xlsheet.Cells(3, 1)
xlLastRow = xlsheet.Cells.Find("*", xlsheet.Cells(1), _
xlFormulas, xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then
MsgBox "error code finding last row" & vbCrLf & _
"Error Number:" & vbTab & Err.Number & vbCrLf & _
"Error Desc:" & vbTab & Err.Description
GoTo Cleanup
End If
Next j

Exit Sub
Cleanup:
End Sub

Sub Test()
Dim i As Integer

For i = 1 To 3
Call A("C:/TestBook1.xls")
Next i

End Sub[/vba] And when I don't use the "On Error Resume Next" I find that the xlLastRow = Empty as well as xlWhole, xlByRows, and xlPrevious.....

Perhaps I'm missing some of your code....

EDIT: I'm using Excel 2003, if that makes a difference.

[jindon]

Hi

I guess what you getting is when find method find nothing.

Try to confirm there is a value somewhere by
x=application.counta(xlsheet.cells)
msgbox x

if x<>0 then something is really wrong, else you might cleared the sheet...

[malik641]

Any word on this?