Find next cell that contains

[FirstVette52]

I need to put a search box on a spreadsheet that allows the user to type in
values and click a button to search for the next cell that contains whatever
is entered, in part or in whole, whether at the beginning, middle, or end,
and is not case sensitive.

The 'box' is cell A3 and the range to be searched is any cells that contain
values (numbers or text). I'm just getting rolling with VBA so I'm not too
good with coding or modifying these macros. But the macro in this case will
be tied to a button beside the cell.

The goal is to have the look and feel of a Web Search tool that won't glitch
when the user enters junk.

Thanks in advance for any help!

 

[Don Guillett]

Look in the vba help index for FIND and FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message
news:[email protected]...

 

[FirstVette52]

Been there, done that, and I was able to put together code for the initial
'Find', but what I am wanting to do is use a single piece of code to find the
first value, and then continue (on the next button click) to find the next
value, and the next, ...

Thx!

 

[FirstVette52]

Here is my code for the initial 'FIND':
Sub SEARCH()
Dim c As Range
Dim s
s = Range("A3").Value
Set c = Cells.Find(What:=s, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
c.Select
End Sub

It works great, but it's insufficient for what I'm wanting to accomplish and
I'm not good enough with this code to get to the next level.

 

[Don Guillett]

Did you look for FINDNEXT as I suggested

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message

 

[FirstVette52]

Yes, thanks, I did. But I'm not proficient enough with the code to take it to
the next level.

This is what I currently have for 'FINDNEXT':

Sub SEARCHNEXT()
Dim v
v = Range("A3").Value
With Worksheets(1).Range("a1:z500")
Set c = .Find(v, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
End If
End With
End Sub

 

[Chip Pearson]

I have a procedure called FindAll that will find all occurrences of a
value within a range and returns a Range containing all of the found
cells. You could use that procedure to get all the matches and then
simply jump from one found cell to the next. For example,

Sub AAA()

Dim FoundCells As Range
Dim Res As VbMsgBoxResult
Dim R As Range

Set FoundCells = FindAll(SearchRange:=Range("A10:K100"), _
FindWhat:=Range("A3"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False)

If Not FoundCells Is Nothing Then
For Each R In FoundCells
Application.Goto R, True
Res = MsgBox("Item found. Continue?", vbYesNo)
If Res = vbNo Then
Exit For
End If
Next R
Else
MsgBox "Value not found.", vbOKOnly
End If

End Sub


You can download the FindAll function module from
http://www.cpearson.com/excel/FindAll.aspx . The functionality of
FindAll is also wrapped into an add-in, available at
http://www.cpearson.com/excel/FindAllXLA.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[FirstVette52]

Thanks Chip; your site was where I started my search. It was an impressive
add-in, but too complex for myusers, that's why I was trying to 'dumb it
down' to a single field, single button operation. The concensus was that all
were familiar with the Web search boxes and wanted something similar, except
that multiple clicks would take you to the next occurance & etc.

Thanks for your help

 

[FirstVette52]

OK I copied and pasted your Function 'FindAllOnWorksheets' into a new Module,
but executing the code gave me an 'Undefined' error for the 'Attribute
VB_Name...' code at the very beginning (sorry, I'm not too good at this).

 

[Don Guillett]

It appears that you did NOT use all of the example. Be advised that if your
v is in row 3 and you are looking from row1 to 500, you will also fine your
V

Sub SEARCHNEXT()
Dim v
v = Range("a3").Value
With Range("a1:z500")
Set c = .Find(What:=v, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByrows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do


MsgBox c.Row
'the rest of your code here


Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com> wrote in message

Yes, thanks, I did. But I'm not proficient enough with the code to take it
to
the next level.

This is what I currently have for 'FINDNEXT':

Sub SEARCHNEXT()
Dim v
v = Range("A3").Value
With Worksheets(1).Range("a1:z500")
Set c = .Find(v, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
End If
End With
End Sub

 

[Chip Pearson]

You need to Import the bas file, not copy/paste out of Notepad.
Attribute statements are compiler directives that VBA uses but are not
entered directly in the module. In VBA, go to the File menu, choose
Import File, navigate to where you saved the bas file, and open it. It
will create a module named modFindAll in your project.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[FirstVette52]

Thanks; I'm going to go back and do it the way you suggested, but I played
with it last night and deleted the first two lines. After that, the code ran
well enough for me to tailor results. But I want to get it running the way it
should with the Attribute statements included.

Thank you for your help! You guys motivate me to keep learning the code.