Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > cells.find function does not work!


Reply
 
Thread Tools Display Modes
  #1  
Old 09-16-2003, 10:51 AM
Frizzell Frizzell is offline
Newcomer
 
Join Date: Sep 2003
Posts: 8
Default cells.find function does not work!


My problem is this. I have a number of sheets with football team names on them, and I need to find the cell address of any particular club. So I record the macro as I perform the task; selecting all the relevant sheets, and going to edit/find. This works perfectly while I record it, but when I run the following code I get the error message: 'Run-time error 91. Object variable or With Block variable not set'
This is the code I have recorded is:

Sheets(Array("Belgium", "Brazilian", "Danish", "Dutch", "English", "French", "German", "Irish", "Italian", "Norwegian", "Portugese", "Scottish", "Spanish", "Swedish", "Turkish")).Select

Cells.Find(What:="Glasgow Rangers", After:=ActiveCell,LookIn:=xlValues,LookAt:=xlPart,SearchOrder:=xlByRow s, SearchDirection:=xlNext, MatchCase:=False).Activate


If there is an easier way of searching for a text string in numerous sheets I would be grateful to hear it. Any help would be grately appreciated.

P.S. I can't use a lookup function, as some sheets have numerous tables on them so I have to use the find function.
Reply With Quote
  #2  
Old 09-16-2003, 01:36 PM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Try this:

Code:
Sub findTeam() Dim sh As Worksheet Dim found As Range For Each sh In ActiveWorkbook.Sheets Set found = sh.Cells.Find("Glasgow Rangers", , xlValues, , , , False) If Not (found Is Nothing) Then sh.Select found.Select Exit Sub End If Next End Sub

Reply With Quote
  #3  
Old 09-16-2003, 04:30 PM
tboltfrank tboltfrank is offline
Senior Contributor
 
Join Date: Jul 2003
Posts: 1,022
Default

Hi Mark007 - that's a nice, piece of code.

Would it take you much to modify it to keep looking for additional match's, each time you run it?
Reply With Quote
  #4  
Old 09-17-2003, 03:28 AM
gzsprakt gzsprakt is offline
Freshman
 
Join Date: Aug 2003
Location: Frankfurt, Germany
Posts: 36
Default

like that?
Code:
Sub findTeam() Dim sh As Worksheet Dim found As Range For Each sh In ActiveWorkbook.Sheets Set found = sh.Cells.Find("Glasgow Rangers", , xlValues, , , , False) If Not (found Is Nothing) Then sh.Select found.Select Call someOtherSub Do Set found = .FindNext(found) If found Is Nothing Then Exit Do found.Select Call someOtherSub Loop While Not found Is Nothing End If Next End Sub
Reply With Quote
  #5  
Old 09-17-2003, 04:10 AM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Yes though you probably need a check in there on the loop that checks the found cell address isn't the same as the first found cell address.
Reply With Quote
  #6  
Old 09-17-2003, 04:21 AM
gzsprakt gzsprakt is offline
Freshman
 
Join Date: Aug 2003
Location: Frankfurt, Germany
Posts: 36
Default

True, but I didn't know how to check for that address in the beginning sheet only, if you know what I mean. I was just lazy
__________________
Please,use the [vb ] [/vb ] tags to display your code
Reply With Quote
  #7  
Old 09-17-2003, 05:40 AM
Frizzell Frizzell is offline
Newcomer
 
Join Date: Sep 2003
Posts: 8
Default Thanks (One last array question)

Many thanks to those who have helped with this, particularly Mark007.

I have one small request for help, then my code is done.

Rather than search the whole workbook, I can eliminate some sheets (which I know will have the team name in and thus possibily confuse matters). So in essence I just need an array of correct sheets; something like the code below. But what do I declare 'myshts' as?, and how to I populate 'myshts' with the correct infromation?

Dim myshts As Object
Dim sh as worksheet

myshts = Sheets(Array("Belgium", "England", "etc"))

For Each sh In myshts

'code to search for team'

Next


Cheers

H
Reply With Quote
  #8  
Old 09-17-2003, 06:03 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

Could go like this :
Code:
Dim MySheets As Variant, i As Integer MySheets = Array("Belgium", "France") For i = LBound(MySheets) To UBound(MySheets) Sheets(MySheets(i)).Find(blablabla etc code... Next i
On how to populate that array of sheets ??
Hmm maybe you could store the names of the sheets you want to
search in a column and loop through those names so there is no array needed.
Like :
Code:
Private Sub CommandButton1_Click() Dim i As Integer 'assumes the sheetnames stored in column a of the sheet with this button on For i = 1 To Range("A1").End(xlDown).Row Sheets(Cells(i, 1).Value).Find(blablabla etc code... Next i
Reply With Quote
  #9  
Old 09-17-2003, 07:43 AM
Frizzell Frizzell is offline
Newcomer
 
Join Date: Sep 2003
Posts: 8
Default Thanks

Many thanks,

Code works beautifully.

Cheers to all

H

p.s. Sorry, I didn't know about the vb tags for including code
Reply With Quote
  #10  
Old 09-23-2003, 06:16 AM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Quote:
Mark,

Would you mind posting a (find next) version of your code at this link where the user was looking for his football teams. - Your code works great to find the first instance, but I have not been able to get anyone elses code (in that thread) to work and having that routine be able to find the next match, each time it runs, is something that I have a good use for.

Many Thanks - Frank
Ok Frank, I think this should meet your needs. I was trying to think of a way that didn't involve me rewriting the find function and also didn't involve select but in the end I couldn't so here is the solution I came up with. You need to run Multifind to run a search and then when you want to run a different new search run the newSearch macro. Obviously it would be much improved to add a modeless userform that has a textbox and findnext button as per the actual find facilaty as this would negate the need to have 2 macros.

Anyway enough talk, here it is:

Code:
Dim firstAddress As String Dim x As String Sub newSearch() x = vbNullString firstAddress = vbNullString MultiFind End Sub Sub MultiFind() Dim stIndex As Integer Dim curIndex As Integer Dim lastIndex As Integer Dim found As Range If x = vbNullString Then x = InputBox("Enter search string:") End If If Len(Trim(x)) Then stIndex = ActiveWorkbook.ActiveSheet.Index curIndex = stIndex lastIndex = ActiveWorkbook.Worksheets.Count Do Set found = ActiveWorkbook.Worksheets(curIndex).Cells.Find(x, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False) If found Is Nothing Or firstAddress = getAdd(found) Then If InStr(1, ActiveWorkbook.Worksheets(curIndex).Range("a1").Value, x, vbTextCompare) > 0 Then Set found = ActiveWorkbook.Worksheets(curIndex).Range("a1") End If curIndex = curIndex + 1 If curIndex > lastIndex Then curIndex = 1 End If firstAddress = vbNullString ActiveWorkbook.Worksheets(curIndex).Select ActiveWorkbook.Worksheets(curIndex).Range("a1").Select Set found = Nothing ElseIf firstAddress = vbNullString Then firstAddress = found.Address End If Loop Until Not (found Is Nothing) Or curIndex = stIndex If found Is Nothing Then MsgBox "Item not found" Else found.Select End If End If End Sub Function getAdd(ob As Object) As String If ob Is Nothing Then getAdd = vbNullString Else getAdd = ob.Address End If End Function

Hope that does what you were after.

Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
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

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
need help! i'm a beginner geekool General 7 04-26-2003 01:44 AM
HDC generater does not work why?? piggybank1974 Game Programming 3 07-22-2002 11:44 AM
Shareware Registry Protection Technigue karachi999 General 2 01-21-2002 03:40 PM
MS Winsocket gibson General 3 07-20-2001 11:03 AM
link function to textboxes golash General 2 05-03-2001 12:57 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->