Hello there. I'm trying to write a macro(vba) script which shows the lowest price for each SKU(ItemNumber) I enter. I have 5 columns: SKU(ItemNumber), Itemtitle, Supplier1Price, Supplier2Price, Supplier3Price. Suppliers have their own prices. The main struggle is that I don't know how to write the code to show me multiple results I enter.
This is the code:
Option Explicit
Sub LowPrice()
Dim lr As Long, i As Long, j As Integer
Dim ans As Double, sku As String, col As Variant
Dim number As Integer, numeris As String
j = 1
Application.ScreenUpdating = False
lr = Range("A" & Rows.Count).End(xlUp).Row
number = InputBox("How many SKUS you gonna enter?")
While j <= number
sku = InputBox("What SKU to search?")
j = j + 1
Wend
For i = 2 To lr
If Range("A" & i) = sku Then
sku = WorksheetFunction.Min(Range("C" & i & ":G" & i))
End If
Next i
MsgBox ("Minimum Price is " & sku)
Application.ScreenUpdating = True
End Sub
Attached a file to make it more understandable.
Hope someone can help me. Thank you.
Hi sigitas
Give this a try.
I have added 2 helper columns F and G to make the coding a bit easier.
Good luck
Sunny
Hello there, thank you for your help, but it doesn't work the way it should work. If I try to change the SKU to the real one - the search box shows that there is no such SKU.
Added a picture to show how it looks like.
It is not working because the SKU you attached with your file is text but the actual is a number.
The codes is based on what you supplied earlier.
Please try the new attachment.
If it is still not working then please attach the actual SKU in a file for us to take a look.
Sunny
Hello. Sorry that I'm wasting your valuable time, I appreciate your help. It works very well, but the problem is that I want to add more SKU's at once, like 10 to make the work more effective. I tried to do this on my own but unfortunately failed.
Other than the 10 SKU that you need to see at once, is the file that I gave you working properly?
Yes, it works perfectly
Furthermore I am wondering why you want a user form to do the searches.
You could have easily done it using Data Validation (DV) and VLOOKUP in another sheet.
Advantage of such a approach:
1) Easy to implement/troubleshoot
2) Can add as many DV/VLOOKUP as you want
3) Can print out the results.
4) Require almost no VBA/macro (unless you want to clear the DV with VBA instead of manually).
Sunny
Do you know the way I can do it? I started to do it with formulas, but failed. From the very start I wanted to do it the easiest way possible.
I will attach a picture and explain how I really want it to look like.
In the right you can see the table with headers: SKU Example, Qty, Price, Supplier
On the left you can see a database with all the info.
We get many invoices from the customers with the SKU's and quantities they want.
My aim is just simply copy/paste SKU's to the column from the invoice and the formula should look for the lowest price for the certain SKU and show the supplier.
SKU and qty are added manually.
OK now I understand why you wanted so many SKU. In this case using a userform is not recommended.
Please refer attached.
You only need to enter/paste the SKU numbers you need into column A of the ENQUIRY sheet.
It uses an Excel Table so the formula auto-populate when you enter any data into column A.
If you need to know more about Tables then refer here https://www.myonlinetraininghub.com/excel-tables
Good luck.
Sunny
Thank you very much for your help! I really appreciate it. Have a good day!
No problem.
