Hi,
First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to.
What I want to do:
Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the part number column to evaluate the price at a given quantity range for a particular part number. If the price is lower than the current price, add it to a data set. When done cycling through the table, return the results to a summary page. Only data that has newer dates (ie, less than 6 months old) will be evaluated.
Given (hard-coded in macro for now):
Quantity Ranges
6 month age restriction for evaluating the data.
I thought about creating a dictionary for the pat numbers, then a nested dictionary for the quantity ranges. The item for the quantity range would be a collection of price, quote date, and supplier. While this seems logical in my head, it is another thing to compile in vba. I can't seem to access and/or modify the collection for a given part number at a given quantity range to replace a collection record (price, date, supplier) with new data.
Here is my code.
and the class clsPrice has the following variables:
Thanks for any help or guidance on this!
First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to.
What I want to do:
Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the part number column to evaluate the price at a given quantity range for a particular part number. If the price is lower than the current price, add it to a data set. When done cycling through the table, return the results to a summary page. Only data that has newer dates (ie, less than 6 months old) will be evaluated.
Given (hard-coded in macro for now):
Quantity Ranges
6 month age restriction for evaluating the data.
I thought about creating a dictionary for the pat numbers, then a nested dictionary for the quantity ranges. The item for the quantity range would be a collection of price, quote date, and supplier. While this seems logical in my head, it is another thing to compile in vba. I can't seem to access and/or modify the collection for a given part number at a given quantity range to replace a collection record (price, date, supplier) with new data.
Here is my code.
VBA Code:
Sub ReportLowestCostperRangeQty()
'if the cost point is from the same supplier, use the most current date for cost @ qty
'if there are multiple suppliers at the same cost point, use lowest cost @ qty for a given time period (6 months)
Dim dataArr As Variant
Dim ws As Worksheet
Dim dataLo As ListObject
Dim targetArr As Variant
Dim qty(0 To 6)
Dim i, j, q, vend As Integer
Dim partDict As Dictionary
Dim qtyDict As Dictionary
Dim qtyStr As String
Dim coll As clsPrice
'Dim supplierDict As Dictionary ' not used
'Dim dateDict As Dictionary ' not used
qty(0) = 1
qty(1) = 5
qty(2) = 10
qty(3) = 25
qty(4) = 50
qty(5) = 100
qty(6) = 250
Set ws = ActiveWorkbook.Worksheets("Data")
Set dataLo = ws.ListObjects("data")
Set partDict = New Dictionary
dataArr = dataLo.DataBodyRange
ReDim targetArr(1 To 5, 1 To 1)
For i = 1 To UBound(dataArr, 1)
Select Case dataArr(i, 6)
Case qty(0) To qty(1)
qtyStr = qty(0) & " - " & qty(1)
Case qty(1) + 1 To qty(2)
qtyStr = qty(1) & " - " & qty(2)
Case qty(2) + 1 To qty(3)
qtyStr = qty(2) & " - " & qty(3)
Case qty(3) + 1 To qty(4)
qtyStr = qty(3) & " - " & qty(4)
Case qty(4) + 1 To qty(5)
qtyStr = qty(4) & " - " & qty(5)
Case qty(5) + 1 To qty(6)
qtyStr = qty(5) & " - " & qty(6)
Case Is > qty(6)
qtyStr = qty(6) & "+"
End Select
If CDate(dataArr(i, 2)) > CDate(DateAdd("m", -6, Date)) Then
If Not partDict.Exists(dataArr(i, 1)) Then
Call partDict.Add(dataArr(i, 1), New Scripting.Dictionary)
Set qtyDict = New Dictionary
Set coll = New clsPrice
coll.price = dataArr(i, 7)
coll.qdate = dataArr(i, 4)
coll.supplier = dataArr(i, 8)
qtyDict.Add qtyStr, coll
Else
If Not qtyDict.Exists(qtyStr) Then
Set coll = New clsPrice
coll.price = dataArr(i, 7)
coll.qdate = dataArr(i, 4)
coll.supplier = dataArr(i, 8)
qtyDict.Add qtyStr, coll
Else
If coll.price >= dataArr(i, 7) Then
coll.price = dataArr(i, 7)
coll.qdate = dataArr(i, 4)
coll.supplier = dataArr(i, 8)
Set qtyDict(qtyStr) = coll
End If
End If
End If
Set partDict(dataArr(i, 1)) = qtyDict
End If
Next i
' will be a clean up section to set all objects to nothing before ending.
End Suband the class clsPrice has the following variables:
VBA Code:
Public supplier As String
Public qdate As Date
Public price As CurrencyThanks for any help or guidance on this!
