VBA: Trying to create a macro for "FIND"

[bamaisgreat]

I was trying to create a Macro to have a box popup to find something on the worksheet. I have the code below but I think it needs adjusted.

Sub find()
'
' find Macro
'
What = InputBox("word to search")
Sheets().Select
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
   
End Sub

It would be nice if the search was not case sensitive.
Also if not to difficult a way to "Find Next" also.

 

[Rick Rothstein]

Maybe these two macros will do what you want...

Sub FindWord()
  Dim What As String
  Application.FindFormat.Clear
  What = InputBox("word to search")
  Cells.Find(What, LookAt:=xlPart, MatchCase:=False).Select
End Sub

Sub FindNextWord()
  Cells.FindNext(ActiveCell).Select
End Sub

 

[bamaisgreat]

Thanks Rick, Is it it possible to have the macros combined somehow ?

 

[Rick Rothstein]

Thanks Rick, Is it it possible to have the macros combined somehow ?

I don't see how... you would either have to ask the "Word to search" question everytime you run the macro or, if you did not want to do that, you would only be able to search for one word (the word you answered to the question).

 

[bamaisgreat]

Ok, How about if I created a userform with a search button and a next button. Could the code be modified to do that ??

 

[Rick Rothstein]

Ok, How about if I created a userform with a search button and a next button. Could the code be modified to do that ??

When the code was behind a button, I sort of understood why you might want to do what you asked for, but now you are proposing calling up a UserForm and that leads me to ask... why not simply use the built-in Find dialog box that Excel provides? You can either call it up from the menu or Ribbon depending on which version of Excel you have or, more simply to my mind, just press the Ctrl+H keystroke combination?

 

[bamaisgreat]

Thanks for the reply. On the Sheets I am using the ribbon is hidden. There are several different users that look at the sheet and most of them no nothing of Excel.So I have tried to make it as simple as possible. The sheet has several Command Buttons on it now for Basic Functions.

 

[Rick Rothstein]

Thanks for the reply. On the Sheets I am using the ribbon is hidden. There are several different users that look at the sheet and most of them no nothing of Excel.So I have tried to make it as simple as possible. The sheet has several Command Buttons on it now for Basic Functions.

Okay, this macro calls the built-in Find dialog which has Find and FindNext built into a single dialog box meaning you can assing the macro to a single button (like you wanted originally) and still have both functionalities. Give it a try and see if it will work out for you...

Sub ShowFindDialogBox()
  Application.Dialogs(xlDialogFormulaFind).Show
End Sub

 

[bamaisgreat]

Thanks Rick that hit the spot..

 

[norts55]

Is there a way to have this dialog show the "Find and Replace" dialog? ...The one with a button to "Find All"