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]

Above you mentioned using a CLEAR button to remove the highlighted cells, we don't have that button anywhere for us to press.
I may have missed something at the beginning when building the Userform or I may have not included this button.
Are you able to look at this function whether I'm doing something wrong here.

As I described:

To keep the highlight when exiting the Userform, uncheck the "Remove highlighting on exit" checkbox. To remove the highlight, open the userform again, check the checkbox, and then exit.

but if you want you want you can add a button to clear the format. I'll amend the code.

and would love the search/find to be between columns B through to K - searching cells range B9:K1008

Ok, I'll amend the code.

 

[GomaPile]

Sorry, the code is flawed, I'll try to fix it.

There's no need to apology, however I'm so glad you can understand my writing... lol lol.
I've learnt a lot what you have created we're all happy here, it's very simple to use, and there's no rush either.


Cheers,
Nasa2

 

[GomaPile]

but if you want you want you can add a button to clear the format. I'll amend the code.

Wow!! Yes please...

 

[GomaPile]

Hey Akuini,

Just checking would it be possible WHEN we activate the Userform - start typing Find What... can those cells immediacy highlight as you type.

 

[Akuini]

@GomaPile
Ok, here's the updated version:
1. Added: CLEAR BUTTON in the sheet.
2. Added: Go to the start button in the userform.
3. Removed: FIND ALL button in the userform.
4. Set the search only on Range("B9:K1008").
5. start typing Find What... those cells immediately highlight as you type

The workbook:

Akuini - Find & Highlight via Userform #2

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[GomaPile]

Hey Akuini,

Sorry, we're able to download or open the attachment workbook file: Userform #2.

Would it be possible for you to add the vba codes here that way I can copy & paste.

Its sounds like you have been busy and thanks for your amazing work too. Can't wait to see what you have created.

Cheers,
Nasa2

 

[Akuini]

Sorry, we're able to download or open the attachment workbook file: Userform #2.

Are you saying you can't download it? or you can download it but can't open it?

 

[GomaPile]

Akuini,

Can't download it - cos my hospital blocks certain sites or links

Can't open it - tried using my mobile, was able to download it but can't open it as per attachment.

 

[GomaPile]

Akuini,

I managed to figure it out. You had 2 file extensions xlsm & xlsx added to the file

I can confirm its opened

Cheers

 

[GomaPile]

OMG!! you are amazing. Just had play and you're f%$king awesome. OMG, I must show this to our Manager's