VBA for multi-criteria find using a UserForm?

[iAmPatch]

Hi

I've created an inventory tracker with the help of @My Awser Is This
The inventory tracker has 3 worksheets, Dashboard, Released Items and Purchase Orders
In the Dashboard worksheet, I already have 2 command buttons (Add Released Items and Add Purchase Order) which pops up UserForms respectively

I would like to ask for assistance in coding a third command button which when clicked on, would pop up another UserForm wherein the user would key in specific search criteria (Vendor and Item). Once done, the user would then click on the "Search" button and the code should search in Purchase Orders sheet. If the value of the searched item in Purchase Orders is "0" or there is no item match; user should be routed to the Purchase Order UserForm. But if there is an available item, the user should be routed to the Released Items UserForm.

I tried using the record-macro option and fooling around with the "Find" function of Excel; but Excel didn't record the "Find" steps I did ...

Is this possible to do?

Thanks

 

[My Aswer Is This]

Well step one to show the userform you would use something like this:
Userform1.show

But if you have already built a userform I would assume you already know this

Then if you have a button on your userform

And a Textbox on your userform you enter the value you want to search for in the textbox

But its best if you narrow down your search Range.
It's best to say search column B on sheet name Mom
If you just say search sheet Mom then the script has to search the entire sheet which will take a long time.

And you said search for value and if value if nothing meaning it found nothing or found zero then do this.

If we are look for a value like 25 well if there is no value 25 but then 25 equals zero that's not possible as far as I know.

Like: Look for Billy and if there is no Billy or Billy equals Jane then do this

But here is a script that may help you.

Private Sub CommandButton1_Click()
Dim SearchString As String
Dim SearchRange As Range
SearchString = TextBox1.Value
Set SearchRange = Sheets("Me").Range("A1:A20").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then UserForm2.Show: Exit Sub
UserForm3.Show
End Sub

 

[iAmPatch]

Hi @My Aswer Is This

The code you shared was flawless And I updated it again to match the information I have currently.

Private Sub Command_Search_Click()

Dim SearchString As String
Dim SearchRange As Range
SearchString = TB_SearchValue.Value
Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then PurchaseOrder.Show: Exit Sub
ReleasedItems.Show

End Sub

Question though, is it possible to show a message saying how many items are still on hand when the search is successful; prior to showing the ReleasedItems UserForm? Like if we search for "MGMPencil", and the is indeed an MGMPencil in Purchase Orders sheet, with 500 items on hand (This would be found in Column F of the Purchase Orders sheet). It will show a message, "Hey, there's still 500 of this that you can giveaway". Then the person would click on "Ok"; then the ReleasedItems UserForm will show up

 

[My Aswer Is This]

In my example I used:

Set SearchRange = Sheets("Me").Range("A1:A20")​

But in your script your searchRange was:

Set SearchRange = Sheets("Purchase Orders").Range("A1").Find(SearchString,​

Which means only look in A1
If that is what you want to do then we can do this another way. We just look at A1.

But you earlier said in your last post:

"Search" button and the code should search in Purchase Orders sheet.​

That sounded like you wanted to do a search looking for something.

As now it sounds like you only want to look in A1.

 

[iAmPatch]

Hi @My Aswer Is This

I'm sorry, I updated the SearchRange to "A:A". I've edited my previous post as well. The code now is

Private Sub Command_Search_Click()

Dim SearchString As String
Dim SearchRange As Range
SearchString = TB_SearchValue.Value
Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then PurchaseOrder.Show: Exit Sub
ReleasedItems.Show

End Sub

 

[My Aswer Is This]

I'm still confused. Are we expecting to find this item more then once.
And are we looking in column A for the name of the item and looking in column F for the quanity found?

Because your search range Is column A but then you say:

and the is indeed an MGMPencil in Purchase Orders sheet, with 500 items on hand (This would be found in ​

Column F ​

of the Purchase Orders sheet)​

 

[iAmPatch]

Ideally we are just searching for the unique value in Column A. Which I think is already satisfied by the:

"Set SearchRange = Sheets("Purchase Orders").Range("A:A").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole"

and there should be duplicate values appering in Column A.

I was just thinking if it's possible to add in a second condition wherein if there is indeed an item match in Column A; there would be a message that would notify the person that there are still X number of items available for releasing. The X number of items is found in Column F.

 

[My Aswer Is This]

So your saying if Duck is found in A20 then if F20 has 45
This means we have 45 Ducks.
And you want a message box to popup saying we have 45 ducks.
Is that correct?

 

[iAmPatch]

Yes, that is correct. Or the message box could just say "We still have 45 of those, click "Ok" for release form

 

[My Aswer Is This]

I will work on that.
Just curious why you need 4 Userforms.

I have been working with Userforms for years and have never found a need for more then one.

When lets say userform me popsup what will the user see different from what he may see when userform You popsup.