lifeson
06-27-2008, 12:25 AM
I need help with the logic for this routine.
I am after a routine that will give the correct price for an item that may have a number of different prices depending on the area(patch) the item is sold and the 'Product' it is sold in.
The rules are:
An item can have either a National Price or a regional price.
If the item has a regional price the national price is ignored.
The item can also be sold in different product types:
the product hierachy is:
pType2 is a child of pType1 which is a child of CHA
Only one price is going to be valid and the price returned in order of preference is
pType2 and patch price
pType1 and patch price
CHA and patch price
pType2 and National price
pType1 and National Price
CHA and National PriceSo the routine should do the following:
Check the item has any prices at all
If no prices exist the price is ?0.00 - end routine
If the item has at least one price then
check if there is a patch price
If there is no patch price then
check for National Price
If there is no price @ CHB then
check for a price @ CHP
If there is no price @ CHP then
Check for price @ CHA
When the routine runs I know:
The itemID
The patchID
pType1 and pType2
This is as far as I got before my head started to hurt:banghead: and I am sure that If then statements are probably not the best way forward.
Sub QryPrice(packID As String, compID As String, pType1 As String, pType2 As String)
Dim patch As String, pbID As String, msg As String
Dim ws As Worksheet
Dim r As Long, x As Long
Dim price As Single
Set ws = ThisWorkbook.Worksheets("price")
pbID = 02 'frmQuoteBuilder.cboPbandID
r = ws.Cells(Rows.Count, "A").End(xlUp).Row
If r = 1 Then
MsgBox "No price exists"
price = Format(0, "?#,##0.00")
Else
'A price exists, now select correct price
'what price band to use
With ws
.Select
For x = r To 2 Step -1
MsgBox "Checking row " & x
With .Cells(x, "H")
If .Value = pbID Then
MsgBox "A patch price exists, check product"
If .Value = pType2 Then 'CHB etc
price = .Cells(x, "I").Value
GoTo Finish
Else 'CHP or CHF
price = .Cells(x, "I").Value
GoTo Finish
End If
Else 'N1' price
MsgBox "No patch price use National"
If .Value = pType2 Then 'CHB etc
price = .Cells(x, "I").Value
GoTo Finish
Else 'CHP or CHF
price = .Cells(x, "I").Value
GoTo Finish
End If
End If 'check for patch price
End With
Next x
price = .Cells(2, "I").Value
End With
End If 'Check if price exists
Finish:
MsgBox "Price carried forward is: " & price
End Sub
In the example the patchID is 02 and the product is CHB so the only valid price returned should be ?500
Hope this makes sense and someone can help:think:
I am after a routine that will give the correct price for an item that may have a number of different prices depending on the area(patch) the item is sold and the 'Product' it is sold in.
The rules are:
An item can have either a National Price or a regional price.
If the item has a regional price the national price is ignored.
The item can also be sold in different product types:
the product hierachy is:
pType2 is a child of pType1 which is a child of CHA
Only one price is going to be valid and the price returned in order of preference is
pType2 and patch price
pType1 and patch price
CHA and patch price
pType2 and National price
pType1 and National Price
CHA and National PriceSo the routine should do the following:
Check the item has any prices at all
If no prices exist the price is ?0.00 - end routine
If the item has at least one price then
check if there is a patch price
If there is no patch price then
check for National Price
If there is no price @ CHB then
check for a price @ CHP
If there is no price @ CHP then
Check for price @ CHA
When the routine runs I know:
The itemID
The patchID
pType1 and pType2
This is as far as I got before my head started to hurt:banghead: and I am sure that If then statements are probably not the best way forward.
Sub QryPrice(packID As String, compID As String, pType1 As String, pType2 As String)
Dim patch As String, pbID As String, msg As String
Dim ws As Worksheet
Dim r As Long, x As Long
Dim price As Single
Set ws = ThisWorkbook.Worksheets("price")
pbID = 02 'frmQuoteBuilder.cboPbandID
r = ws.Cells(Rows.Count, "A").End(xlUp).Row
If r = 1 Then
MsgBox "No price exists"
price = Format(0, "?#,##0.00")
Else
'A price exists, now select correct price
'what price band to use
With ws
.Select
For x = r To 2 Step -1
MsgBox "Checking row " & x
With .Cells(x, "H")
If .Value = pbID Then
MsgBox "A patch price exists, check product"
If .Value = pType2 Then 'CHB etc
price = .Cells(x, "I").Value
GoTo Finish
Else 'CHP or CHF
price = .Cells(x, "I").Value
GoTo Finish
End If
Else 'N1' price
MsgBox "No patch price use National"
If .Value = pType2 Then 'CHB etc
price = .Cells(x, "I").Value
GoTo Finish
Else 'CHP or CHF
price = .Cells(x, "I").Value
GoTo Finish
End If
End If 'check for patch price
End With
Next x
price = .Cells(2, "I").Value
End With
End If 'Check if price exists
Finish:
MsgBox "Price carried forward is: " & price
End Sub
In the example the patchID is 02 and the product is CHB so the only valid price returned should be ?500
Hope this makes sense and someone can help:think: