Find Function

[GomaPile]

Hi all, how is everyone doing, it’s been a while since my last post.

Is there someone who’s able to help us that would be super appreciated for our Hospital Uniform Department.

VBA below was found browsing through Google, weblink provided will take you there (it’s safe).

The VBA does what it supposed to do which is find data within the excel, and we did add our bit too.

The VBA is very similar to the build-in FIND FUNCTION that you and I have on all computers today.

Though the difference between the VBA one from Google and build-in FIND FUNCTION both have their Pros & Cons, but we would love to see them All-In-One. Well to be openly 100% honest to everyone, we’re hoping that someone can help us who knows VBA. Anyways, I tried myself – but sadly no luck.

Build-in FIND FUNCTION:

• Pros: goes straight to that cell and cycles through to the next matching info & onwards.
• Cons: doesn’t highlight the cells yellow.

VBA from Google:

• Pros: highlights all matching info in yellow.
• Cons: though it doesn’t go straight to the first cell or cycles through to the next.

Only 3 things we are requesting, if possible, it can be done.

• Highlight all matching cells in Yellow and go to the first highlighted Cell
• The ability to cycle Back and Forth onto the next cell
• Range: only lookup in Columns C and D

'Website https://www.extendoffice.com/documents/excel/5839-excel-search-and-highlight-results.html

Private Sub CommandButton1_Click()

Dim xRg As Range
Dim xFRg As Range
Dim xStrAddress As String
Dim xVrt As Variant

Application.EnableEvents = False
Application.ScreenUpdating = False

Sheets("Orders").Unprotect Password:="test" '---- change the password to your liking's
xVrt = Application.InputBox(prompt:="Search:", Title:="Search Tool...")
If xVrt <> "" Then
Set xFRg = ActiveSheet.Cells.Find(what:=xVrt)
If xFRg Is Nothing Then
MsgBox prompt:="Cannot find this employee", Title:="Search Tool Completed..."
Exit Sub
End If
xStrAddress = xFRg.Address
Set xRg = xFRg
Do
Set xFRg = ActiveSheet.Cells.FindNext(After:=xFRg)
Set xRg = Application.Union(xRg, xFRg)
Loop Until xFRg.Address = xStrAddress
If xRg.Count > 0 Then
xRg.Interior.ColorIndex = 6
If xRsp = vbOK Then xRg.Interior.ColorIndex = xlNone
End If
End If
xRg.Areas(xRg.Areas.Count)(1).Select
Sheets("Orders").Protect Password:="test" '---- change the password to your liking's

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Regards,
Gomapile (NASA2)

 

[Akuini]

@GomaPile
Did you get a chance to test my last code 'Akuini - Davary_Tools_0.7_to_test' in post #43?

 

[Alex Blakenburg]

@Alex Blakenburg
Could you test my latest macro in post #43? I'm attempting to incorporate this macro into an add-in, so I need feedback to identify bugs and gather ideas for improving its functionality and performance.

It seems to do what the form indicates it will do. The only thing I could not work out was what the "Start searching after n characters" was meant to do.

 

[Alex Blakenburg]

I did a bit more testing, it highlights as you are typing and if you then continue to add a character that invalidates the search, it blanks out the count but does not release the highlighting.
On a large data set you can see it flash every time you enter a character but that is probably ok, given what it does.

 

[Akuini]

The only thing I could not work out was what the "Start searching after n characters" was meant to do.

Thank you for reviewing my code.
To make this feature work, you need to check the last checkbox.

but I think I will remove the last checkbox and set the number (in the textbbox) to 1 as the default.

I did a bit more testing, it highlights as you are typing and if you then continue to add a character that invalidates the search, it blanks out the count but does not release the highlighting.

Yes, it's a bug, thanks for pointing that out.

 

[Alex Blakenburg]

No problem. Glad I was of some help.
PS: I did try checking the tick box and putting in different values but it didn't seem to do anything differently.

 

[Akuini]

PS: I did try checking the tick box and putting in different values but it didn't seem to do anything differently.

Not, sure why it didn't work on your side. When you set it to 2 and you only type 1 char in the textbox then the code shouldn't do the highlighting & the label above the textbox should say "Please, type more character".
I'll look into it.

 

[Alex Blakenburg]

Not, sure why it didn't work on your side. When you set it to 2 and you only type 1 char in the textbox then the code shouldn't do the highlighting & the label above the textbox should say "Please, type more character".
I'll look into it.

I think it's more likely that I just didn't understand that, that was how it worked.

 

[Akuini]

I think it's more likely that I just didn't understand that, that was how it worked.

Actually the text "Start searching after N character:" should be more clear, probably it should say: "Start searching after N character is typed: N="
I included this feature to deal with large data. Searching in large data could be slow, it will be faster if the keyword consists of 2 or more characters.

 

[Akuini]

@GomaPile
Did you get a chance to test my last code 'Akuini - Davary_Tools_0.7_to_test' in post #43?