double V-Lookup

[Djblois]

I am trying to compare prices on 2 seperate worksheets. Here is the Criteria:

1) First it needs to look at the Item# (B) column in the Report Tab
2) Then it needs to find that number (D) in the Price List Tab
3) then it needs to look at the Whse column (I) in the Report tab and Depending on the warehouse it will pick which column to compare the price to in the price list (I:K)
4) If it is (NJ01 will be East Coast (I)), (FLs01 and MAE2 will be central (J)), (CAs82 will be West Coast(K)) There are more Warehouses for each column this is just a sample
5) Then it needs to Highlight the Price Column in the Report tab if it is less then the Price in the Price List Depending on the column it chose
**These two tabs will actually be in seperate workbooks - Just did it this way to simplify things.


Here is a sample workbook:

[Bob Phillips]

You need to do two thinsg to make this work easily.

First, get rid of the merged cells in Report column B, and repeat the Item # in each row, looking back is a pain.

Second, add a lookup table of Whse codes against the value, to facilitate the lookup.

[Djblois]

Xld,

Thank you

First, those cells aren't merged-I copied past values from a pivot table
Second, Since it is a pivottable I will move the item# into the (EJ) Column so it populates on every row. Except the Total rows which don't need it.

[Bob Phillips]

That's good, what about the lookup table for the Whse code?

And what do you want to do if the Item# is not found, as with 25-5235?

[Djblois]

I would want it to do nothing then but with the full Price List it should always be found unless it is a new item (Which shouldn't be often)

[Djblois]

Just bumbing it up so it doesn't get lost

[Djblois]

Please Help

[Djblois]

This is the code I have come up with

Sub TestPrice()

Dim eastCoast As Variant, central As Variant, westCoast As Variant
Dim wasPriceListOpen As Boolean

eastCoast = array("NJ01", "NJH6")
central = array("COA5", "FLnR7", "FLn34", "FLs01", "GAG2", "MAE2", "MID5", "PAC2", "PA78", "SCC4", "TX05", "TX27")
westCoast = array("CAn67", "CAn99", "CAsE3", "CAs04", "CAs06", "CAs07", "CAs82")

Application.DisplayAlerts = False
On Error Resume Next
Set Wb(2) = Workbooks("Grocery Price List 2007 Current.xls")
wasPriceListOpen = True
If Err = 9 Then
Set Wb(2) = Workbooks.Open("H:\@PriceList\Grocery\Grocery Price List 2007 Current.xls")
wasPriceListOpen = False
End If
Err.Clear
Application.DisplayAlerts = True

For i = 2 To finalRow
If pvt.Range("H" & i) = eastCoast Then
If pvt.Range("I" & i) < "VLOOKUP(pvt.RC[-2],'[Grocery Price List 2007 Current.xls]C4:C11,5,FALSE)" Then
pvt.Range("I" & i).Interior.ColorIndex = 15
End If
ElseIf pvt.Range("H" & i) = central Then
If pvt.Range("I" & i) < "VLOOKUP(pvt.RC[-2],'[Grocery Price List 2007 Current.xls]C4:C11,6,FALSE)" Then
pvt.Range("I" & i).Interior.ColorIndex = 15
End If
ElseIf pvt.Range("H" & i) = westCoast Then
If pvt.Range("I" & i) < "VLOOKUP(pvt.RC[-2],'[Grocery Price List 2007 Current.xls]C4:C11,7,FALSE)" Then
pvt.Range("I" & i).Interior.ColorIndex = 15
End If
End If
Next

If wasPriceListOpen = False Then
Wb(2).Close
End If

End Sub

It looks like it would work but it is highlighting every single row in the price column

[Djblois]

Can Someone please help me. The code I tried looks logical to me but it doesn't work. Please Help

[Djblois]

Can anyone let me know if this can be done? Xld was helping me yesterday. I am closer to the solution now but it isn't fixed yet. Is this possible? Or did I do something to offend people on here?

[Djblois]

Hello anyone? is this possible?

[mdmackillop]

I'll bump this up, and have a look tomorrow if it's not solved by then.

[CCkfm2000]

still needs some more work but looks promising.

see attached file.

[Djblois]

Thank you both, it would be greatly appreciated if I you can help me with this.

[CCkfm2000]

Ok here is an updated version.
got a bit long winded with the fomular....
Will this do? : pray2: : pray2:

[Djblois]

CCkfm2000,

That looks interesting. Only problem is this is something that will be done a few times a week, maybe more by multiple people, so I need to get the operation to work with code without the extra added columns if needed. I greatly appreciate the help and I will see if I can figure out how to get it to work with code. Also, if anyone else has any suggestions please don't hesitate to comment.

[CCkfm2000]

sorry thats the best i can do.

i'm not very good with vba but i'm sure that someone will look at the formulas and code it for you. if its the results was what you where looking for.

[mdmackillop]

Give this a test. I've added a messagebox for info during testing
Option Explicit

Dim msg As String

Sub TestPrice()

Dim wasPriceListOpen As Boolean
Dim WB As Workbook, pvt As Worksheet, PLS As Worksheet
Dim FinalRow As Long, i As Long
Dim Cel As Range
Dim ThsBook As Workbook

Application.DisplayAlerts = False
On Error Resume Next
Set ThsBook = ThisWorkbook
Set WB = Workbooks("PriceListBook.xls")
Set pvt = ActiveWorkbook.Sheets("Report")
FinalRow = pvt.Cells(Rows.Count, "I").End(xlUp).Row
wasPriceListOpen = True
If Err = 9 Then
Set WB = Workbooks.Open("C:\AAA\PriceListBook.xls")
wasPriceListOpen = False
End If
Err.Clear
Application.DisplayAlerts = True
ThsBook.Activate
Set PLS = WB.Sheets("Price List")

For Each Cel In Range(Cells(2, "I"), Cells(FinalRow, "I"))
If Cel <> "" Then
Select Case WHLoc(Cel.Value)
Case "East"
CheckPrice PLS, Cel, 5: msg = "East"
Case "Cent"
CheckPrice PLS, Cel, 6: msg = "Central"
Case "West"
CheckPrice PLS, Cel, 7: msg = "West"
End Select
End If
Next

If wasPriceListOpen = False Then
WB.Close
End If

End Sub

Function WHLoc(Data As String) As String
Dim EastCoast, Central, WestCoast, tmp As Long
EastCoast = Array("NJ01", "NJH6")
Central = Array("COA5", "FLnR7", "FLn34", "FLs01", "GAG2", "MAE2", "MID5", "PAC2", "PA78", "SCC4", "TX05", "TX27")
WestCoast = Array("CAn67", "CAn99", "CAsE3", "CAs04", "CAs06", "CAs07", "CAs82")
On Error Resume Next
tmp = Application.WorksheetFunction.Match(Data, EastCoast, 0)
If tmp > 0 Then
WHLoc = "East"
Exit Function
End If
tmp = Application.WorksheetFunction.Match(Data, Central, 0)
If tmp > 0 Then
WHLoc = "Cent"
Exit Function
End If
tmp = Application.WorksheetFunction.Match(Data, WestCoast, 0)
If tmp > 0 Then
WHLoc = "West"
Exit Function
End If
End Function

Sub CheckPrice(WS As Worksheet, Cel As Range, Oset As Long)
Dim WHPrice As Single
WHPrice = WS.Columns(4).Find(Cel.Offset(, -7)).Offset(, Oset)
If Cel.Offset(, 1) < WHPrice Then
Cel.Offset(, 1).Interior.ColorIndex = 33
Else
Cel.Offset(, 1).Interior.ColorIndex = xlNone
End If

MsgBox "Item " & Format(Cel.Offset(, -7), "00-0000") & vbCr & msg & _
" Warehouse price is " & Format(WHPrice, "$##0.00") & vbCr _
& "Report price is " & Format(Cel.Offset(, 1), "$##0.00")
End Sub

[mdmackillop]

:whistle:

[mdmackillop]

Is this solved?

[Djblois]

Sorry,

Mdmackillop, I had more pressing issues with my add-in. I will probably get to this next week.