MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with simple ByVal procedure

  Asked By: Anita    Date: Aug 23    Category: MS Office    Views: 627

I'm trying to think of a code to this problem but I am stuck. If
anyone could extend help or advice it would be great.

I need a procedure that is executed each time, and when values change
in the specified range those would need to be verified if they are
within allowed range. If cellChanged values is empty, empty also the
Total Sales cell in row of cellChanged and then exit the procedure.

Get product ID from same row of cellChanged - use Offset method?
Look up product ID in column range named "Product_ID" – use Find
Get low and high prices from row containing product ID – use Offset?
Assign cellChanged.value to price
If price is >= low price range and price is <= high price range
total price

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 5 And Target.Count = 1 Then
Call validatePrice(Target)
End If
End Sub

Sub validatePrice(cellChanged As Range)

If (cellChanged.Value = Empty) Then
cellChanged.Offset(0, 1).Value = Empty
Exit Sub
End If

If (cellChanged.Value <> Empty) Then
' ??? need to look up a price range by product id from
another table
End If

End Sub



4 Answers Found

Answer #1    Answered By: Nicolas Costa     Answered On: Aug 23

You need to use WorksheetFunction.VLookup twice to find the low and high
price, then do the compares, then do whatever you want with the price if it
is/is not within range.

Worksheetfunctiuon/VLookup needs a range  as its second parameter. You can use
named  range in the spreadsheet but you need to express it as


(If I didn't tell you that you would waste a couple of hours wondering why the
function was not working!)

Answer #2    Answered By: Djoser Massri     Answered On: Aug 23

this would work just fine, but I need
a different method of looking up the low/high price.

The first of the two tables looks like this:

Column A B C D E F
Date | Product ID | Prod Name | Units Sold | Sales Price | Total

When i enter a price in column  E, I need the code  to look up the
ProductID in the same row  (i.e cellChanged.Offset (0, -3)) and with
that ProductID I would need to look up low/high prices  in the second
table that resides indifferent worksheet. that table looks as follows:

Col. A B C D
Product ID | Product Name | Low price | high  price |

Hope this makes more sense. I think I need to use the Find method but
I am not sure how. If you can help?

Answer #3    Answered By: Sherrie Thomas     Answered On: Aug 23

I think VLookup will work fine.

Let us assume that the product  table is a named range  called "tblProductList"
on a worksheet called "Products"

then use:

HighPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" &
Target.Row), Worksheets("Products").Range("tblProductlist"), 4, false)

LowPrice = WorksheeetFunction.VLookup(ActiveWorksheet.range("B" & Target.Row),
Worksheets("Products").Range("tblProductlist"), 3, false)

If Target.Value>LowPrice and Target.Value<HighPrice Then

'Do something with it

End If

(The long lines will undoubtedly get wrapped and you will need to unwrap them.
I have left balnk line between each line of code.)

Answer #4    Answered By: Anselma Schmidt     Answered On: Aug 23

You were right, it worked just fine. Thank you for all the help

Didn't find what you were looking for? Find more on Help with simple ByVal procedure Or get search suggestion and latest updates.