Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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! -- FirstVette52 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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 "Don Guillett" wrote: Look in the vba help index for FIND and FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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! -- FirstVette52 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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. -- FirstVette52 "Don Guillett" wrote: Look in the vba help index for FIND and FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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! -- FirstVette52 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
Did you look for FINDNEXT as I suggested
-- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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 "Don Guillett" wrote: Look in the vba help index for FIND and FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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! -- FirstVette52 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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 -- FirstVette52 "Don Guillett" wrote: Did you look for FINDNEXT as I suggested -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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 "Don Guillett" wrote: Look in the vba help index for FIND and FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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! -- FirstVette52 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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) On Thu, 19 Feb 2009 08:49:01 -0800, FirstVette52 <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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 "Chip Pearson" wrote: 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) On Thu, 19 Feb 2009 08:49:01 -0800, FirstVette52 <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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). -- FirstVette52 "Chip Pearson" wrote: 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) On Thu, 19 Feb 2009 08:49:01 -0800, FirstVette52 <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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 "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 -- FirstVette52 "Don Guillett" wrote: Did you look for FINDNEXT as I suggested -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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 "Don Guillett" wrote: Look in the vba help index for FIND and FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software "FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote in message ... 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! -- FirstVette52 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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) On Thu, 19 Feb 2009 14:03:02 -0800, FirstVette52 <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote: 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). |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
You can save the address of each cell found in turn and set the code to search after this address. Sub SEARCH() Dim c As Range Dim s If Range("M1") = "" Then Range("M1") = "$A$3" s = Range("A3").Value Set c = Cells.Find(What:=s, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False, _ After:=Range(Range("M1"))) c.Select Range("M1") = c.Address End Sub -- mdmackillop ------------------------------------------------------------------------ mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66340 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find next cell that contains
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. -- FirstVette52 "Chip Pearson" wrote: 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) On Thu, 19 Feb 2009 14:03:02 -0800, FirstVette52 <(My User name is Firstvette 52, too) firstvet52@(my ISP E-mail provider is) netzero.com wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
UDF code to find specific text in cell comments, then average cell values | Excel Programming | |||
use find twice to find cell on a specific row | Excel Programming |