Runtime 91 error due to cells.find statement !??!

canoe414 · Joined: 04 Oct 2007 Posts: 28

Greetings all,

My VB6 w/OLE Automation works fine until it comes to the last line
listed, then burps with a "Runtime Error 91 Object variable or With
block variable not set" message

Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application
'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
'--- do 4th sheet
Set xlWrksheet = xlApp.Worksheets(5)
xlWrksheet.Activate

Dim cells_found As Range
cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
here

cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
syntax, also fails

What am I doing wrong?

TIA,

Steve

Archived from group: microsoft>public>vb>ole>automation

NickHK · Joined: 04 Oct 2007 Posts: 251

Hint: cells_found is an object.

But also, you should add a check that a match was actually found:
If cells_found Is Nothing Then MsgBox "No Match"

NickHK

wrote in message@f16g2000cwb.googlegroups.com...
> Greetings all,
>
> My VB6 w/OLE Automation works fine until it comes to the last line
> listed, then burps with a "Runtime Error 91 Object variable or With
> block variable not set" message
>
> Dim xlApp As Excel.Application 'early-binding
> Dim xlWrkbook As Excel.Workbook
> Dim xlWrksheets As Excel.Worksheets
> Dim xlWrksheet As Excel.Worksheet
> Set xlApp = New Excel.Application
> '--- open checklist spreadsheet
> Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
> '--- do 4th sheet
> Set xlWrksheet = xlApp.Worksheets(5)
> xlWrksheet.Activate
>
> Dim cells_found As Range
> cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
> here
>
> cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
> syntax, also fails
>
>
> What am I doing wrong?
>
> TIA,
>
> Steve
>

JLatham · Joined: 04 Oct 2007 Posts: 1

Nick HK has hit two nails right on their heads. I can verify what he said
from actual, recent experience. cells_found does need to be an object and if
'foobar' is not found, then the .Find() will not generate an error, but the
first time you try to refer to any property of cells_found after that will
also result in Error 91 because cells_found is Nothing.

"canoe414@telecom-digest.zzn.com" wrote:

> Greetings all,
>
> My VB6 w/OLE Automation works fine until it comes to the last line
> listed, then burps with a "Runtime Error 91 Object variable or With
> block variable not set" message
>
> Dim xlApp As Excel.Application 'early-binding
> Dim xlWrkbook As Excel.Workbook
> Dim xlWrksheets As Excel.Worksheets
> Dim xlWrksheet As Excel.Worksheet
> Set xlApp = New Excel.Application
> '--- open checklist spreadsheet
> Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\mysprdsht.xls")
> '--- do 4th sheet
> Set xlWrksheet = xlApp.Worksheets(5)
> xlWrksheet.Activate
>
> Dim cells_found As Range
> cells_found = xlWrksheet.Cells.Find(What:="foobar") <-- fails
> here
>
> cells_found = xlWrksheet.Cells.Find("foobar") <-- tried thys
> syntax, also fails
>
>
> What am I doing wrong?
>
> TIA,
>
> Steve
>
>

canoe414 · Joined: 04 Oct 2007 Posts: 28

JLatham (removethis) wrote:
> Nick HK has hit two nails right on their heads. I can verify what he said
> from actual, recent experience. cells_found does need to be an object and if
> 'foobar' is not found, then the .Find() will not generate an error, but the
> first time you try to refer to any property of cells_found after that will
> also result in Error 91 because cells_found is Nothing.

OK I changed the Dim of cells_found to an Object and still got the same
'91' error
on the cells.find line.

Trying something simpler, I removed that stuff and added a simple
collection
and populating it with some cell values...

Dim lastrow As Integer
Dim xlApp As Excel.Application 'early-binding
Dim xlWrkbook As Excel.Workbook
Dim xlWrksheets As Excel.Worksheets
Dim xlWrksheet As Excel.Worksheet
Set xlApp = New Excel.Application

'--- open checklist spreadsheet
Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\sprdsht.xls")

'--- do DM250_TC sheet
Set xlWrksheet = xlApp.Worksheets(4)
xlWrksheet.Activate
tmpStr = xlWrksheet.Name

Dim channelCol As Collection
tmpStr = xlWrksheet.Cells(80, 3).Value <-- shows a value
"J40.45"
tmpStr = xlWrksheet.Cells(80, 4).Value <-- shows a value
"J32.76"
' good so far....
For i = 80 To 1103
channelCol.Add xlWrksheet.Cells(i, 4).Value,
xlWrksheet.Cells(i, 3).Value <-- fails
Next i

AArrrrggghhh!!! Trying to add to the collection fails with a '91' error
message too!!!, and on the first hit in the loop when i = 80, where
just before the loop a real value is found in that cell. What the hell?

Thx again,

Steve

canoe414 · Joined: 04 Oct 2007 Posts: 28

adding
Set channelCol = New Collection
in the appropriate place didn't help either..... (no more coding at
01:40 for me)

NickHK · Joined: 04 Oct 2007 Posts: 251

That's not the problem.
A Range is an object, same as Excel.Workbook, Excel.Worksheet, etc. You
correctly use "Set" for those variables, so use the same syntax for the
range object :
Set cells_found = xlWrksheet.Cells.Find(What:="foobar")

NickHK

wrote in message@e3g2000cwe.googlegroups.com...
>
> JLatham (removethis) wrote:
> > Nick HK has hit two nails right on their heads. I can verify what he
said
> > from actual, recent experience. cells_found does need to be an object
and if
> > 'foobar' is not found, then the .Find() will not generate an error, but
the
> > first time you try to refer to any property of cells_found after that
will
> > also result in Error 91 because cells_found is Nothing.
>
> OK I changed the Dim of cells_found to an Object and still got the same
> '91' error
> on the cells.find line.
>
> Trying something simpler, I removed that stuff and added a simple
> collection
> and populating it with some cell values...
>
>
> Dim lastrow As Integer
> Dim xlApp As Excel.Application 'early-binding
> Dim xlWrkbook As Excel.Workbook
> Dim xlWrksheets As Excel.Worksheets
> Dim xlWrksheet As Excel.Worksheet
> Set xlApp = New Excel.Application
>
> '--- open checklist spreadsheet
> Set xlWrkbook = xlApp.Workbooks.Open("C:\mystuff\sprdsht.xls")
>
> '--- do DM250_TC sheet
> Set xlWrksheet = xlApp.Worksheets(4)
> xlWrksheet.Activate
> tmpStr = xlWrksheet.Name
>
> Dim channelCol As Collection
> tmpStr = xlWrksheet.Cells(80, 3).Value <-- shows a value
> "J40.45"
> tmpStr = xlWrksheet.Cells(80, 4).Value <-- shows a value
> "J32.76"
> ' good so far....
> For i = 80 To 1103
> channelCol.Add xlWrksheet.Cells(i, 4).Value,
> xlWrksheet.Cells(i, 3).Value <-- fails
> Next i
>
>
> AArrrrggghhh!!! Trying to add to the collection fails with a '91' error
> message too!!!, and on the first hit in the loop when i = 80, where
> just before the loop a real value is found in that cell. What the hell?
>
>
> Thx again,
>
> Steve
>

canoe414 · Joined: 04 Oct 2007 Posts: 28

> A Range is an object, same as Excel.Workbook, Excel.Worksheet, etc. You
> correctly use "Set" for those variables, so use the same syntax for the
> range object :
> Set cells_found = xlWrksheet.Cells.Find(What:="foobar")

Ah, Set, that's the ticket. Problem solved.

Thx to all who responded,

Steve