Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change existing values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change existing values
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) "choice" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change existing values
this will change old values to 10 if col a has 24 and col b matches part
nos. right click sheet tabview codecopy paste thisSAVE 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 -- Don Guillett SalesAid Software "choice" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change existing values
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" wrote: this will change old values to 10 if col a has 24 and col b matches part nos. right click sheet tabview codecopy paste thisSAVE 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 -- Don Guillett SalesAid Software "choice" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to find change and paste existing values/rows in excel with help of form | Excel Discussion (Misc queries) | |||
How to change an existing query | Excel Discussion (Misc queries) | |||
How to add characters to all existing values in a given column | Excel Worksheet Functions | |||
Search for existing values in woorksheet!? | Excel Programming | |||
Change to existing macro | Excel Programming |