Log In
Register
Home
February 13, 2019
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
February 13, 2019
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
February 13, 2019
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
Other than the 10 SKU that you need to see at once, is the file that I gave you working properly?
February 13, 2019
Offline
Yes, it works perfectly
VIP
Trusted Members
June 25, 2016
Offline
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
February 13, 2019
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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.myonlinetraininghu.....cel-tables
Good luck.
Sunny
1 Guest(s)
