I have a form that I need to look down a column of SKU's for partial matches and display each instance on the form. The SKU's are made up of different part numbers for easy reference on what the SKU is. The partial match can be anywhere in the SKU.
For example....using the "Inventory Search" form if you put "6353" in the search box then I want it to return and display on the form each SKU that has that part number in it.
Attached is a sample database.
Thank you,
Your Button "Send SKU's to Sheet" means where in Sheet1?
It can go to sheet2 A1. I'll then modify to the working database accordingly. The code for a partial match is my biggest issue.
Thank you.
This will put the data into the text boxes
Put that into Sheet2 right away (part of this code) or only if using the other command button?
Please Login or Register to view this content.
If you want the data in Sheet2 while populating the various textboxes in the userform, put this right before the "End If" line
AndPlease Login or Register to view this content.
with the other Dim and Set statements.Please Login or Register to view this content.
so you'll end up with
and do away with CommandButton2 or use it for something else.Please Login or Register to view this content.
If that is not what you want, let us know.
May I ask why you choose not to make use of a listbox to filter the search...
You have 4 boxes on your form for allowable matching sku entries...Could be so much more though...
See attached...As you type into search box the data is filtered to only matching criteria displaying in listbox...
From there you can double click on item in listbox and then populate textboxes above to make changes, edits, delete etc...
Please Login or Register to view this content.
Last edited by sintek; 01-31-2022 at 03:06 AM.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
Your code worked great and I realized I didn't need the userform. I simply added a textbox to the sheet with a command button and directly populates the "Sku" into the sheet. I then use vlookup against the listed sku results to pull only the information I need for the report.
Here's what the code ended up looking like on my working database. I commented out the "Me.control..." statements:
Please Login or Register to view this content.
I know I already closed this but I simply am trying to add a message "Not Found" if entered part number isn't found. Iv'e tried all I know on messages but can't get it to work with this code. Below is my latest attempt.
Please Login or Register to view this content.
I thought that you wanted "SKU", "ASIN" and "Description" copied into the other sheet but by the looks of your changed code, you only want SKU copied across. Is that right?
Yes, and I'm using vlookup on the sheet based on the sku results. In my working database there is a sheet where all the information is being pulled from. It's a thousand times larger than the test workbook I uploaded for this thread. My working database has too much proprietary information to post anywhere publicly. Anyways, now I'm just using a sheet text box with a command button on the sheet. I was complicating it by using a form and so I made it more practical and informative. Your code works great and I appreciate your efforts. Couldn't have done it without you. Now all I need to do is figure out this simple message box!
Try following. Change as required.
Please Login or Register to view this content.
This should be faster on a larger range. It is based on your attachment in Post #1.
Change references where required. Replace "6353" by your reference.
Please Login or Register to view this content.
Last edited by jolivanes; 02-02-2022 at 11:43 PM.
I am content with the code below since it seems so much simpler for me to understand...not that I fully understand it and it's working for my needs. I can somewhat get my way around VBA but I'm certainly not a pro. However, I've encountered an issue where I need to add another field of information (the ASIN) in my Sku Report via the same "part_number" text box on the Sku Report sheet. Since I'm using vlookup and the ASIN is left of the searched part number then vlookup doesn't allow looking left of the column referenced. So I need the code below to also pull the ASIN (Amz SKU Inventory, column A) for each associated Sku it finds. In other words, we're searching column B where my ASIN is in column A and I need the ASIN on my report to go into column E of the Sku Report.
I appreciate your help and patience in getting me through this!
Please Login or Register to view this content.
I created a mock database which fully mimics my working database. See attached. Called "Mock Test DB Part Number Search".
To reiterate. When I do a part number search for sku's containing the part number I want the resultant sku's and ASIN to be posted to the Sku Report. For the other fields I'm simply using vlookup formulas from the resulting sku's....maybe not the best way but it's the way I understand it best. I hope the new sample DB makes things easier. I'd also like the message if a part number isn't found. Hopefully the code will be simple enough for me to understand. There's alot of stuff in the suggested codes that have gone way over my head.
In a nutshell, on Sku Report sheet you will enter a part number, hit search button and it will go down column b of Amz SKU Inventory sheet and pull out all the sku's that include the part number. For instance, this SKU example has 4 part numbers: 5901-63780-6353-6356 In my working database there are 7 sku's that include part number 5901.
If you change this line
to these linesPlease Login or Register to view this content.
Does that give you the desired result?Please Login or Register to view this content.
With this, you don't need any formulas.
Try it on a copy of your workbook (or the attachment from Post #14, delete all formulas)
Copy the code into a regular module and run macro from either worksheet.
Please Login or Register to view this content.
Last edited by jolivanes; 02-04-2022 at 12:31 AM.
That works great! But there are 3 issues:
1. One issue is I cannot us a dash in the query. A 5 digit part number might include the numbers in a 4 digit search. In other words, if a part number were 63191 and I only wanted to search 6319 then I would put a dash after 6319 because ALL my sku part numbers will have a dash behind them. With example below you can see how I would search 6319-
63191-1pk
63191-2pk
6319-1pk
6319-2pk
2. After I typed the above paragraph I also realized it doesn't seem to allow alpha characters. i.e, "c1501726"
3. If a part number is not found then I get a "Run time Error, Subscript out of Range".
Last edited by Andy C.; 02-04-2022 at 10:06 AM.
The goalposts keep on moving so see if you can attach a workbook with ALL possible scenarios, including what you mention in your last post about starting with alpha chars.
Add all examples that you could possibly search for manually.
BTW, "That works great! But there are 3 issues:" refers to my last post (Post #16) I assume.
Last edited by jolivanes; 02-04-2022 at 03:55 PM.
In the meantime, try this and let us know what is missing.
Please Login or Register to view this content.
Working like a Champ! Can't find anything else at this point.
Thank you for your magic!
Andy
Thanks for letting us know Andy
Good Luck
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks