PDA

View Full Version : What routine to use?



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:

Bob Phillips
06-27-2008, 12:45 AM
I am sure this can be done with a simple formula, but where is the national and patch price held. And what about VHA and NI?

lifeson
06-27-2008, 01:11 AM
The prices in the example are the result of a query that returns all the active prices for the item (BLR2068) where the product type is pType1 Or pType2 Or 'CHA'

pType1 and Ptype2 can change but 'CHA' will always be constant

Unfortunately I can't narrow the query down by patch because the source data is set up incorrectly with a table for prices and a table for patches, but in the patch table there is no patch record with a national ID (NI) so for example if I searched by patch ID 02 and there was only a National (NI) price set up, no results would get returned

Bob Phillips
06-27-2008, 01:21 AM
UH, and the natinal and product patch is where again?

lifeson
06-27-2008, 01:28 AM
UH, and the natinal and product patch is where again?

Sorry, I thought you meant how was it generated

Still not sure what you mean by "where is it"

Do you mean in the spreadsheet?

The prices to search are in column "F" and the patchID's are in Column "E"

Apologies if we have our wires crossed here

Bob Phillips
06-27-2008, 01:41 AM
Still not sure that I understand what is going on or why, but I see why you get 0. Try this



Sub QryPrice()
Dim qry As String, 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")

pType1 = "CHP"
pType2 = "CHB"

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
For x = r To 2 Step -1
MsgBox "Checking row " & x
With .Cells(x, "E")
If .Value = pbID Then
MsgBox "A patch price exists, check product"
If .Value = pType2 Then 'CHB etc
price = .Offset(0, 1).Value
GoTo Finish
Else 'CHP or CHF
price = .Offset(0, 1).Value
GoTo Finish
End If

Else 'N1' price
MsgBox "No patch price use National"
If .Value = pType2 Then 'CHB etc
price = .Offset(0, 1).Value
GoTo Finish
Else 'CHP or CHF
price = .Offset(0, 1).Value
GoTo Finish
End If
End If 'check for patch price
End With
Next x
price = .Cells(2, "F").Value
End With

End If 'Check if price exists
Finish:
MsgBox "Price carried forward is: " & price

End Sub

Bob Phillips
06-27-2008, 02:13 AM
Maybe this will help you with --

In A2, enter A123

Then in B2, use =ISNUMBER(RIGHT(A2,3))

Then in C2, use =ISNUMBER(--RIGHT(A2,3))

lifeson
06-27-2008, 02:29 AM
I think we are at crossed purposes here :bug:
I knew this was going to be hard to explain :(

The code you posted returns the value ?1200

The patch ID is 02
pbID = "02" 'frmQuoteBuilder.cboPbandID

I have used 02 in this example but could be any patchID (01 -25)

As there is only 1 record with a patch price of 02, the price returned should be ?500

If there was no record with a patch price and there were only N1 prices then the value would be ?1000

In the example there are 2 records with an N1 price

row 4: CHB N1 ?1000
row 5: CHA N1 ?1200

But because the rules say use the lower level first of the hierachy "CHB is a child of CHP which is a child of CHA"
the correct price to use is from row 4

There could be as many as 6 records or none or just 1

Bob Phillips
06-27-2008, 02:35 AM
Okay I can see how to get 500 in this PARTICULAR case, no VBA needed but VBA does it too, but I know your other criteria are more complex and I still do not get the difference between National and patch price, I only see one price column.

Give me a series of values to lookup and the resultant price.

lifeson
06-27-2008, 03:40 AM
Okay I can see how to get 500 in this PARTICULAR case, no VBA needed but VBA does it too, but I know your other criteria are more complex and I still do not get the difference between National and patch price, I only see one price column.

Give me a series of values to lookup and the resultant price.

Thanks for taking the time and effort to understannd this problem
Attached is the same worksheet but with more example on the second sheet Hope that explains it better