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

 

[Kenneth Hobson]

I guess you would need an elaborate Userform to do that.

To simply run the Find dialog:

Application.CommandBars("Edit").Controls("Find...").Execute

 

[Peter_SSs]

See if this comes close. Have your button (I have assumed ActiveX Command Button) run this code.
After the button is clicked to trigger the code, enter the search text and use 'OK' to work like 'Next' and either Cancel or the X at the top right of the input box to work like 'Exit'
With Ctrl+F there are options about matching case, matching entire cell contents etc that you didn't tell us what settings you wanted so they may need tweaking in this code.

Private Sub CommandButton1_Click()
  Static s As String
  Dim rCurr As Range
  
  s = InputBox("Enter Search Text", , s)
  If Len(s) > 0 Then
    If Intersect(ActiveCell, Columns("B:C")) Is Nothing Then
      Set rCurr = Cells(Rows.Count, "C")
    Else
      Set rCurr = ActiveCell
    End If
    Columns("B:C").Find(What:=s, After:=rCurr, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
  Else
    ActiveCell.Activate
  End If
End Sub

 

[My Aswer Is This]

Peter:
I saw your answer and thought wow lets see how this works.
using the normal Ctrl+F
Lets you search for cake for example and if the one it finds is not the one you want you just click find Next and it looks for the next cake. Your script does not provide that option. And I do see your script restricts the search to Columns B and C and also if I search for Cake and the value Cake is not in the range or anywhere on the sheet I get a error Run time error 91

But maybe this was what the User wanted and I just did not understand that. Using Excel 2013

 

[My Aswer Is This]

I do see now after reading original posters question he did want the search restricted to columns B and C

 

[Peter_SSs]

using the normal Ctrl+F
Lets you search for cake for example and if the one it finds is not the one you want you just click find Next and it looks for the next cake. Your script does not provide that option.

No it doesn't, mine requires 2 clicks instead of 1 to do a Find Next.

.. and also if I search for Cake and the value Cake is not in the range or anywhere on the sheet I get a error Run time error 91

Good point, I should have at least allowed for that.

Private Sub CommandButton1_Click()
  Static s As String
  Dim rCurr As Range
  
  s = InputBox("Enter Search Text", , s)
  If Len(s) > 0 Then
    If Intersect(ActiveCell, Columns("B:C")) Is Nothing Then
      Set rCurr = Cells(Rows.Count, "C")
    Else
      Set rCurr = ActiveCell
    End If
    On Error Resume Next
    Columns("B:C").Find(What:=s, After:=rCurr, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
  End If
  ActiveCell.Activate
End Sub

 

[Peter_SSs]

.. but if the user wants to more closely replicate the built-in Find functionality, perhaps the button could just use Kenneth's suggestion with an extra line like

Private Sub CommandButton1_Click()
  Columns("B:C").Select
  Application.CommandBars("Edit").Controls("Find...").Execute
End Sub

 

[My Aswer Is This]

Try this script:

Sub My_Find()
'Modified  7/22/2018  2:28:05 AM  EDT
Dim c As Range
Dim Lastrow As Long
Dim aa As String
Dim x As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
aa = InputBox("Search for what")
    For Each c In Cells(1, 2).Resize(Lastrow, 2)
        If c.Value = aa Then
            x = x + 1
            Application.Goto c
            ans = MsgBox("Is this what you wanted?", vbYesNo)
            If ans = vbYes Then: Exit Sub
        End If
    Next
If x < 1 Then MsgBox aa & "  Value not found": Exit Sub
MsgBox "Found  " & aa & " But none you wanted. Try again if you want"
End Sub

 

[Rick Rothstein]

.. but if the user wants to more closely replicate the built-in Find functionality, perhaps the button could just use Kenneth's suggestion with an extra line like

Private Sub CommandButton1_Click()
  Columns("B:C").Select
  Application.CommandBars("Edit").Controls("Find...").Execute
End Sub

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).

 

[Peter_SSs]

Try this script:

Sub My_Find()
'Modified  7/22/2018  2:28:05 AM  EDT
Dim c As Range
Dim Lastrow As Long
Dim aa As String
Dim x As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
aa = InputBox("Search for what")
    For Each c In Cells(1, 2).Resize(Lastrow, 2)
        If c.Value = aa Then
            x = x + 1
            Application.Goto c
            ans = MsgBox("Is this what you wanted?", vbYesNo)
            If ans = vbYes Then: Exit Sub
        End If
    Next
If x < 1 Then MsgBox aa & "  Value not found": Exit Sub
MsgBox "Found  " & aa & " But none you wanted. Try again if you want"
End Sub

You mentioned that my code did not really emulate the built-in Find dialog as you could not simply click 'Next', & you were quite correct. This code also does not allow emulation of the built-in Find as it forces the Find to "Match case" and to "Match entire cell contents" whereas the built-in Find has options for both of those. My code had similar issues (though the options were easily modified in the code).

This code may also miss finding values if the data in column B extends further down the worksheet than the data in column C.

@ Anton
On further reflection, why not just get the users to add the 'Find...' button to their Quick Access Toolbars and then they would have the full functionality of the built-in find at the click of a button (though they would have to select columns B:C before initiating the Find dialog if values being searched for could also occur elsewhere on the sheet)?