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
I have decided to write a new version of this macro. In this updated version, I have replaced the Userform with an Inputbox. The reason behind this change is that it's more convenient to navigate through the highlighted cells using buttons in the sheet instead of the Userform.
Here's an example of a macro to find and highlight data via an Inputbox.
Features:

• Partial match search only.
• Case-insensitive search only.
• Highlights matching cells.
• Uses Conditional Formatting instead of changing cell interior color, so it won't affect any cell's interior color.
• If you select a single cell, the entire sheet will be searched.
• If you select multiple cells, only the selected range will be searched.
• You can navigate through the highlighted cells using the PREV & NEXT BUTTON.
• To remove the highlight, press the CLEAR button.
• You can use Asterisk to search, for example: s*h will match Stephanie

The code creates Conditional Formatting and then deletes it when it's no longer needed.
The code uses a specific color for highlighting (via Conditional Formatting), which is set in this part:
' Set the color for highlighting (RGB style).
' Do not use this specific color for any other Conditional Format, as it may get deleted.
Private Const Rgb1 As Long = 253
Private Const Rgb2 As Long = 250
Private Const Rgb3 As Long = 55

The workbook:

Akuini - Find & Highlight via Inputbox #1

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

 

[Jaafar Tribak]

Hi Akuini thanks for the update.

To be honest with you, I personally prefer your UserForm version particularly because it is floating modeless and therefore (whith some code tweaking) it could be made to work on any particular active worksheet w/o the need to have the FIND, PREV and NEXT buttons replicated accross the top in every worksheet ... Also, I find the form more visually appealling.

The add-in should ideally have four buttons in the toolbar: FIND, PREV, NEXT, and CLEAR BUTTON.

I was thinking more like simply adding a new ribbon tab with one single button in it, clicking on which would display the userform.

Another thing I forgot to tell you in my previous post is the scenario where the user may already have some cells whose interior color\CFormatting happen to be yellow in which case it would be difficult to tell the difference between the 'true' yellow cells and the highlighted cells at first glance .

This could be avoided by offering the user the possibility to choose for themselves the highlight color best suited for their specific needs.

I was imagining something looking like this maybe:

The user would simply click on some area in the color picker wheel image for chosing the appropriate highlight color. RGB((253,250,55) would be the default.

With some litte code, the userform could be made expandable\shrinkable if it is considered too wide\tall,

These are just some ideas, I thought I would add here i case you like them.

Regards.

 

[Akuini]

@Jaafar Tribak

• I was thinking more like simply adding a new ribbon tab with one single button in it, clicking on which would display the userform.
• by offering the user the possibility to choose for themselves the highlight color best suited for their specific needs.

Those are some good ideas, and they look nice too. I like them. However, as I mentioned earlier, I'm not capable of turning the macro into an add-in. So, how about my previous offer? Are you willing to create the add-in?

 

[Jaafar Tribak]

@Jaafar Tribak

• I was thinking more like simply adding a new ribbon tab with one single button in it, clicking on which would display the userform.
• by offering the user the possibility to choose for themselves the highlight color best suited for their specific needs.

Those are some good ideas, and they look nice too. I like them. However, as I mentioned earlier, I'm not capable of turning the macro into an add-in. So, how about my previous offer? Are you willing to create the add-in?

When I find the time, I will definitely try to see if I can make it into an add-in.

 

[Akuini]

When I find the time, I will definitely try to see if I can make it into an add-in.

Thanks, I'll be looking forward to it.

 

[GomaPile]

Good morning Akuini,

It's looking good so far, we like what you have done. It's been a fun morning building the Userform and adding the vba etc...

Anyway, we're having an issue with this area of code: CheckBox1.Value = xCheck
We all thought it needed (" ") quotation around it "xCheck", then we got another code error: Compile error: Variable not defined

Another issue, how do we Initialize the UserForm, Im guessing with a button on the excel spreadsheet.
We did tried changing the Name to UserForm_Initialize - but that didn't work
Then we turned to Google for some assistant as a reference guide - but too didn't help either


We're all learning here
Cheers

 

[Akuini]

Good morning Akuini,

It's looking good so far, we like what you have done. It's been a fun morning building the Userform and adding the vba etc...

Anyway, we're having an issue with this area of code: CheckBox1.Value = xCheck
We all thought it needed (" ") quotation around it "xCheck", then we got another code error: Compile error: Variable not defined

Another issue, how do we Initialize the UserForm, Im guessing with a button on the excel spreadsheet.
We did tried changing the Name to UserForm_Initialize - but that didn't work
Then we turned to Google for some assistant as a reference guide - but too didn't help either


We're all learning here
Cheers

Sorry, in post#8 I only posted the code in the userform, my bad. Actually there are also codes in sheet1 code & Module1, you can see that in the example file,
The FIND button is a commandbutton of Activex control (not Form Control), it is assigned to sheet1 code in this part:

Private Sub CommandButton1_Click()
UserForm1.Show vbModeless
End Sub

Anyway, we're having an issue with this area of code: CheckBox1.Value = xCheck
We all thought it needed (" ") quotation around it "xCheck", then we got another code error: Compile error: Variable not defined

In module1:

Public xCheck As Boolean

Range: only lookup in Columns C and D

If you need it to operate only in col C:D then I can amend the code to suit, so you don't need to select the range first before doing the search.

 

[Akuini]

@GomaPile
I've updated the code for Find & Highlight via Userform. I just realized that in the Userform, we don't really need the FIND ALL button because this is a live search. The search is executed every time you type a character in the textbox. So, I've removed the button.

Note:
The Userform is modeless, which means that while the Userform is open, you can still move the focus to the sheet. So, for example, you still can edit a cell's content.

The workbook:
Akuini - Find & Highlight via Userform #2

 

[GomaPile]

Sorry, in post#8 I only posted the code in the userform, my bad. Actually there are also codes in sheet1 code & Module1, you can see that in the example file,
The FIND button is a commandbutton of Activex control (not Form Control), it is assigned to sheet1 code in this part:

Private Sub CommandButton1_Click()
UserForm1.Show vbModeless
End Sub

In module1:

Public xCheck As Boolean

If you need it to operate only in col C:D then I can amend the code to suit, so you don't need to select the range first before doing the search.

Hey Akuini, all good everyone here is sooo happy what you have created.

With those extra added codes, we managed to get your vba to work, awesome thanks.

That said, we've noticed when we activate the Userform - start typing "Find What" in the Textbox nothing is highlighted in yellow... until we check the box. "Remove highlighting on exit"... then cells are highlighted yellow.

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.

Range: only lookup in Columns C and D

As for the Search Columns yes please if it's not too much to ask. Our linen lady says thank-you and very happy what you have made, and would love the search/find to be between columns B through to K - searching cells range B9:K1008


Regards,
Nasa2

 

[Akuini]

@GomaPile
I've updated the code for Find & Highlight via Userform. I just realized that in the Userform, we don't really need the FIND ALL button because this is a live search. The search is executed every time you type a character in the textbox. So, I've removed the button.

Note:
The Userform is modeless, which means that while the Userform is open, you can still move the focus to the sheet. So, for example, you still can edit a cell's content.

The workbook:
Akuini - Find & Highlight via Userform #2

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