Use VBA "FIND" without effecting the previous (and future) manual searches.

[kgkev]

I can't figure out why this has changed.

I run a manual Ctrl+F find to find a 5 digit number,

Run macro that uses a find function

Set Loc = Sheets("comments").Range("a1:a10000").Find(search_string, , , xlWhole)

Once the macro is complete I want to Ctrl+F to search again for the same 5 digit number.

However now the find box is prefilled with the value from search_string.


Is it possible to carry out the find without effecting the manual find box?
or reset the find box back to its previous state?

 

[Fluff]

Not that I'm aware of.

 

[kgkev]

is there an alternative way of finding "search_string" without using the find function?

 

[Fluff]

Try

Sub MyFind()
   Dim Loc As Range
   Dim res As Variant
   Dim search_string As String
   
   search_string = "Devon"
   With Sheets("comments")
      res = Application.Match(search_string, .Range("A1:A10000"), 0)
      If Not IsError(res) Then Set Loc = Range("A" & res)
   End With
End Sub

 

[kgkev]

Nice one, thank you very much.


I enjoyed my little test as well

[COLOR=#333333]Sub MyFind()[/COLOR]   Dim Loc As Range
   Dim res As Variant
   Dim search_string As String
   
   search_string = "Devon"
   With Sheets("comments")
      res = Application.Match(search_string, .Range("A1:A10000"), 0)
      If Not IsError(res) Then Set Loc = [COLOR=#ff0000].[/COLOR]Range("A" & res)
   End With [COLOR=#333333]End Sub[/COLOR]

 

[Fluff]

You're welcome & well done for spotting the error