Emulate "CTRL + F" in VBA to only FIND data

[Anton Jansen]

I have been struggling for two days to find the answer to something which is probably simple to most Excel experts. I want to find data only (not replace) using code with a Next and Exit button (on the input box ??), pretty much like what the the CTRL +F keys do in Excel. The find information would have to be an 'input' of sorts by the user. All the data is on one worksheet and I want to search only in columns B and C. (SendKeys does not work. ) The code should simply find and show the cell as the CTRL + F keys do. No trimming or any other fancy code. I want a button to activate the code so the user does not have to use the CTRL + F keys

 

[My Aswer Is This]

Yes I know my script does not provide all the features of the built in find command. I just like coming up with solutions the best I can. This script does allow you to stop if you find what you want or continue looking.
My row count was a guess. So my new solution would be to use Max(Lastrowb,Lastrowc)
Just my solution. Maybe someone else will have a better one which will do more.

 

[Rick Rothstein]

Since the OP said he only wanted to search a single sheet, an alternate to the red highlighted line of code is this one...

Application.Dialogs(xlDialogFormulaFind).Show

which has the small advantage that the user does not have to click the "Options>>" button in order to be able to specify that the search should be case sensitive or that the text being searched for is the only text in the cell. Another advantage is that you can specify the default options directly. For example, if you wanted to specify the text "Peter" as the preloaded default, you would just add it as the first optional argument like this...

Application.Dialogs(xlDialogFormulaFind).Show "Peter"

In addition, if you wanted the "Find what" field to always default to an empty field (the default is for the field to use the last text that was searched for), just specify the empty text string ("") like this...

Application.Dialogs(xlDialogFormulaFind).Show ""

The second optional argument controls the "Look in" search argument... 1 for Formulas, 2 for Values, 3 for Comments. So, if you wanted to search for the text "Peter" in the cell values, you could force those options this way...

Application.Dialogs(xlDialogFormulaFind).Show "Peter", 2

I'll omit the example code for the remaining options as I think they are fully straightforward. The third optional argument controls whether the text being searched for must fill the entire cell or not... 1 puts a check mark in that option's checkbox and 2 omits the check mark. The fourth optional argument controls whether the search order will be "By Rows" or "By Columns"... 1 for "By Rows" and 2 for "By Columns". I could not determine what the fifth argument controlled... it will allow you to specify any number (positive or negative), and only a number, but nothing seems to change when you specify it. The sixth optional argument controls whether the search will be case sensitive or not... 1 puts a check mark in that option's checkbox and 0 omits the check mark (note that the number scheme for this checkbox is... strangely... different than for the "Find entire cells only" checkbox).

About that fifth argument... I decided to search around online and see if there was any information about it. I located a couple of articles which said this argument is supposed to control whether the search is Next or Previous. Those articles seemed to indicate 1 was Next and 2 was Previous, but those numbers do not change the order the search proceeds in for me... I am not sure why as the articles seemed convinced about it. Anyone out there know anything more about controlling this dialog box than what I have managed to discover on my own?

 

[Anton Jansen]

Hello Peter the first 'find' works but the subsequent 'find' keeps selecting the current cell

 

[Peter_SSs]

Hello Peter the first 'find' works but the subsequent 'find' keeps selecting the current cell

The code does not do that for me but note that the search is looking for an exact match of the whole cell contents and also to match case. Is that what you are trying to do? If so, and the code is failing, could you provide a small set of sample data for which the code fails and tell us what text you entered in the input box to search for?

Have you tried any of the other suggestions offered in the thread? If not, I suggest that you do that too.