Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 6th, 2006, 10:39 PM
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default error 91

I am getting this error -

Run time error '91':
Object variable or With block variable not set

and the code that has this error is -
i = Worksheets("Results").Columns(3).Find("Name").Row

the worksheet Results has the text "Name" in some row of column 3.
Do I need to define something... what am I missing ?

Thanks for helping.

Reply With Quote
  #2 (permalink)  
Old November 6th, 2006, 11:11 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I am not as familiar with the Find function, but I usually get this error if I have mispelled the Worksheet Name.

If that is not the case, I would maybe try something like.

Worksheets("Results").Select
ActiveSheet.Columns(3).Find("Name").Row

If that still doesn't work, look to see if you can break the second line into 2 lines, and then see which line the error occurs on to further narrow it down.

Hope that helps,

Mike

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old November 6th, 2006, 11:31 PM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Use a range to get the result of the find

like

Set MyRange =Worksheets("Results").Columns(3).Find(what:="Name ")

If Not MyRange is nothing then
  i = MyRange.row
Else
  ' The text is not found
End if

Cheers
Shasur

http://www.vbadud.blogspot.com
Reply With Quote
  #4 (permalink)  
Old May 2nd, 2007, 02:53 PM
Registered User
 
Join Date: May 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What I have found when you get an error 91, is that the item that you are searching for is not there. I just error trap the code to either put to an error log file or I ignore that problem in the error trapping code.

Reply With Quote
  #5 (permalink)  
Old May 7th, 2007, 11:01 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

The find routine is setting a range value to a variable. This requires proper syntax to work properly without returning an error. Note that I use Option Explicit so I define my variables first.

The proper use of Find routine:
-----------------------------------------------------------------------------------------
'Shows an example on how Find and FindNext work in Excel. Note that in the below example,
' FindNext uses the same range varible for both the solution and the 'seed' value so that
' the next time the same code is executed it would be the second found value being the starting
' point instead of the first found
Dim rFound As Range, iFirstRow As Long, iSecondRow As Long, sMessage As String
Set rFound = Worksheets("SheetName").Range("C:C").Find("Name", lookin:=xlValues) 'Finds first instance only
If Not rFound Is Nothing Then
  iFirstRow = rFound.Row
  Set rFound = Worksheets("SheetName").Range("C:C").FindNext(rFou nd) 'Finds next starting from last found
  If Not rFound Is Nothing Then iSecondRow = rFound.Row 'If no value is found, rFound would be Nothing.
End If
sMessage = "First Found: "
If iFirstRow > 0 Then sMessage = sMessage & iFirstRow Else sMessage = sMessage & "N/A"
sMessage = sMessage & vbCrLf & "Second Found: "
If iSecondRow > 0 Then sMessage = sMessage & iSecondRow Else sMessage = sMessage & "N/A"
MsgBox sMessage
-----------------------------------------------------------------------------------------

Hope this clears things up for you.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 91 GVBGVB Excel VBA 2 April 4th, 2008 12:56 AM
error 91 issues. kd8con Beginning VB 6 1 October 30th, 2006 08:50 AM
Runtime error 91: Apjong Beginning VB 6 1 June 7th, 2006 09:47 AM
runtime error 91 Perseus Beginning VB 6 2 July 30th, 2005 04:36 AM
NTSVC.OCX and Error = 91 skhan22 Pro VB 6 3 October 24th, 2003 01:18 AM



All times are GMT -4. The time now is 05:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.