I am looking for a “Find” macro in order to select/copy cells after a search.
In a spreadsheet you can search columns with FIND.
Result: satisfied or click Find Next. Okay? then Find is closed.
What is the code for this “Find” operation in Excel?
Thanks.
See this link: Excel Find Method in Excel VBA
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
When I run “Record New Macro” I see in the code the input value for “Ctrl+F”.
However I need to find the value in a range of columns.
As a newbie I do not know how to change the code of this macro.
If you know what the code should be, I would be very pleased to see this.
Thanks.
In order the best answer your question please upload a samplw workbook that meets the following criteria:
1. It EXACTLY duplicates the structure of your real workbook, AND
2. It contains representative, non-sensitive dummy data, AND
3. It shows a few clear examples of the expected results to aid in developing the logic of the solution.
- Just a simple spreadsheet with 5 columns (A:F)
- a macro with an inputbox for search criterium
- continue when the match is wrong
- stop the macro when the match is okay
Okay for an expert an easy thing but for a newbie only headache.
Give this a try. It will look only in columns A:F and prompt you for another entry to continue searching.
Please Login or Register to view this content.
How/Where to install the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
Last edited by Palmetto; 05-10-2010 at 04:06 PM. Reason: amended code
Unfortunately the macro stops on Alt+F8/Run.
Compile error: Named argument not found ("Searchformat:=" is highlighted)
I didn't get any errors when testing it before posting, nor just now after copying and pasting the code "as is" into a module in a fresh workbook and running it.
Can't say why you get an error on "SearchFormat:=False".
That bit of code is optional in the Find Method, but syntax is correct as given. You can delete it.
Yes you are right. In another file it works almost perfect.
The macro “stops” after displaying one hit and continues with “Do you want to try another search”.
But this means that you will do another search with another value.
However in my worksheet there are more cells that contains this value as a part.
So I would like to continue the search with the same value until I find the cell with the right content.
Thanks!
I have attached an example “Cars”.
When I search for Opel then the macro stops with the first hit.
There is no option to continue the search to look for other cells with Opel.
I hope this explains a little more my question.
Thanks
Code amended in previous post - works well during testing. It will continue to prompt and move to next matching entry until you choose "No".
The macro “stops” after displaying one hit and continues with “Do you want to try another search”.
When you enter the same input value then the next possible hit is not shown.
“Do you want to try another search” with another input value then there is a new hit.
But again there is no FindNext for another result.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks