Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 02-16-2009, 02:25 PM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
problem with Find next matching cell resolved

The code below is to find matches to the string, word.
If there is more then 1 match in a column the code works fine. If there is only 1 match then it finds this one match twice. Instead of finding the 1 match twice, how do I exit sub if there is no second match?

smiler44


On Error GoTo errorhandler ' if no match found go to errorhandler

Cells.find(what:=word).Activate

If ActiveCell.Text = word Then
MsgBox ("matchfound")
pause (1)
Cells.FindNext(After:=ActiveCell).Activate

errorhandler: Exit Sub ' no match found
End If


Last edited by smiler44; 02-18-2009 at 02:54 PM. Reason: have found the answer
smiler44 is offline   Reply With Quote
Old 02-18-2009, 03:01 PM   #2
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: problem with Find next matching cell

Having done a search on the internet I found some code and have modified it to meet my needs. The original code is at
http://jimtut.blogspot.com/2008/07/v...d-text-in.html

I have modified it to :-

Code:
 
Sub eachtab()
'searches each tab for a match
Dim word As String ' whats in sheet1 col A to find
Dim col As String
Dim counter1 As String
counter1 = 1
col = "a"
both = col + counter1
Range(both).Select
word = Sheet1.Range(both) ' the string to find
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
 
Dim searchText As String
searchText = word
 
Dim r As Range
Dim findNext As Integer
findNext = vbYes
'The firstFind and looped vars are used to determine if we've looped thru every find on this page.
Dim firstFoundCell As String
firstFoundCell = ""
Dim looped As Boolean
looped = False
 
Do While findNext = vbYes And Not looped
' Here's the search!
Set r = Cells.find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
If r Is Nothing Then
' Nothing was found. Exit this WHILE loop (the FOR loop will still go to the next sheet).
Exit Do
Else
r.Activate
' Check/Set the firstFoundCell and the looped variable.
If firstFoundCell = "" Then
firstFoundCell = r.Address
Else
' We've found at least one cell already. Check to see if we've looped back to the firstFoundCell.
If r.Address = firstFoundCell Then
looped = True
Else
End If
End If ' end of checking for firstFoundCell
' If we haven't looped around, prompt the user to findNext. (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
If Not looped Then
'would insert hypa link here?
'findNext = MsgBox("Find Next?", vbYesNo)
MsgBox ("insert hypalink")
End If
End If
Loop ' end of Do While loop
If findNext = vbYes Then
Exit Sub 'MsgBox ("No more matches found!")
End If
 
End Sub
smiler44

Last edited by smiler44; 02-18-2009 at 03:40 PM. Reason: to correct posting
smiler44 is offline   Reply With Quote
Old 02-18-2009, 03:08 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,820 Times in 1,576 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: problem with Find next matching cell

Hey Smiler - It would be great if you could use the CODE tags around your code so it isn't so hard to read and it would keep the indenting (IF you are indenting at all). To do so just place the word CODE between two square brackets [ ] at the start of the code and put /CODE inside two square brackets [ ] at the end of the code.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-18-2009, 03:19 PM   #4
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: problem with Find next matching cell

I have never got the hang of indenting. I can not read code well enough to be able to follow it and associate the top part of the indented code with the bottom part. Sorry I do not understand about the square brackets but should I have posted the code like this?
[code]
Sub eachtab()
'searches each tab for a match
Dim word As String ' whats in sheet1 col A to find
Dim col As String
Dim counter1 As String
counter1 = 1
col = "a"
both = col + counter1
Range(both).Select
word = Sheet1.Range(both) ' the string to find
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''


Dim searchText As String
searchText = word

Dim r As Range
Dim findNext As Integer
findNext = vbYes
'The firstFind and looped vars are used to determine if we've looped thru every find on this page.
Dim firstFoundCell As String
firstFoundCell = ""
Dim looped As Boolean
looped = False

Do While findNext = vbYes And Not looped
' Here's the search!
Set r = Cells.find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
If r Is Nothing Then
' Nothing was found. Exit this WHILE loop (the FOR loop will still go to the next sheet).
Exit Do
Else
r.Activate
' Check/Set the firstFoundCell and the looped variable.
If firstFoundCell = "" Then
firstFoundCell = r.Address
Else
' We've found at least one cell already. Check to see if we've looped back to the firstFoundCell.
If r.Address = firstFoundCell Then
looped = True
Else
End If
End If ' end of checking for firstFoundCell
' If we haven't looped around, prompt the user to findNext. (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
If Not looped Then
'would insert hypa link here?
'findNext = MsgBox("Find Next?", vbYesNo)
MsgBox ("insert hypalink")
End If
End If
Loop ' end of Do While loop
If findNext = vbYes Then
Exit Sub 'MsgBox ("No more matches found!")
End If

End Sub

[code]
smiler44 is offline   Reply With Quote
Old 02-18-2009, 03:28 PM   #5
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,820 Times in 1,576 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: problem with Find next matching cell

No, the last [code] at the very end should have a / between the [ and the c in code.

like this (read the post as shown and follow its instructions):


Also, I am not good at remembering to indent so I use Smart Indenter (FREE) from here:
http://www.oaltd.co.uk/Indenter/Default.htm
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-18-2009, 03:46 PM   #6
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: problem with Find next matching cell

Have modified the original posting of the code and can see it has put it into a "seperate" window that could be easier to follow. There will not be any indents of mine. Will try to remember in future to use the square brackets, sorry.
I'll have a look at the web site you gave me.

smiler44
smiler44 is offline   Reply With Quote
Old 02-18-2009, 03:49 PM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,820 Times in 1,576 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: problem with Find next matching cell

Quote:
Originally Posted by smiler44 View Post
Have modified the original posting of the code and can see it has put it into a "seperate" window that could be easier to follow. There will not be any indents of mine. Will try to remember in future to use the square brackets, sorry.
I'll have a look at the web site you gave me.

smiler44
Thumbs up to you for doing that. It really does help make it easier to read. And definitely look into Smart Indenter as it saves me a whole lot of time worrying about indenting my code and it helps you sometimes find missing End IF's and such as it will sometimes be fairly obvious where there is a missing one when you use the tool and then look at it.



__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Change cell value code problem Keith Excel 4 07-16-2008 06:36 AM
Problem with form to find and populate records whitespace Forms 2 06-25-2008 11:51 PM
Find matching record in adjoining nested tabbed subform cliffrn Macros 0 06-09-2008 11:23 AM
FIND RECORD problem heller General 0 09-28-2004 05:56 PM
Problem with duplicates and matching records Homer J Queries 1 09-03-2004 06:26 AM




All times are GMT -8. The time now is 12:35 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World