change existing values

[Guest]

I have a vlookup table with 3 columns (part number, below 25, above 25)
i sell part number 123456, i get $5 commission (up to 25)
after 25 commission goes to $10 (for all items sold even before 25)
so i have a list:
#24 123456 $5
#25 123456 $10

how can i get it to recalculate the first 24 to $10
(the problem is that i dont want to use equations i need values)
because next month the commission might change and i want these values to
stay the same

thanks in advance

 

[Bob Phillips]

Assuming that your lookup table is on sheet2, and is called commission

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 2 Then
With Target
If .Offset(0, -1).Value < 25 Then
.Offset(0, 1).Value = WorksheetFunction.VLookup( _
.Value, Worksheets("Sheet2").Range("commission"), 2,
False)
Else
.Offset(0, 1).Value = WorksheetFunction.VLookup( _
.Value, Worksheets("Sheet2").Range("commission"), 3,
False)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

this will change old values to 10 if col a has >24 and col b matches part
nos.
right click sheet tab>view code>copy paste this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Offset(0, -1) > 24 Then
For Each c In Range("b2:b" & Cells(Rows.Count, "b").End(xlUp).Row)
If c = Target Then c.Offset(0, 1) = 10
Next
End If
End Sub

 

[Guest]

the tricky part is that i only want it to change the values of this months
entries:
(Columns)
(A) Date (B) Part number (C) Commission

 

[Don Guillett]

The tricky part is that you never mentioned a date in your 3 columns......
a was a number NOT a date
b was part num
c was commission

so add an something like

if month(c.offset(0,-1))=month(now) then