vlookup + macro that doesn't overrwrite old data--need help

[amandabstewart]

My workbook uses both vlookup (price lists) and macros that update a log when I run it.
For example, cost of item A is $10 currently. Using vlookup, prices from the price sheet are entered automatically on a sales sheet. I run the macro that takes that sale info and copies parts of the row to a worksheet for who made the sale—eg Alex’s page would now say $10.
The problem is that if we change the prices on the price sheet and re-run the macro periodically to update new sales, eg cost of item A is changing to $15, it automatically changes the OLD $10 sale to the new value and Alex’s old sales would read $15. I don’t want it to overwrite the old data when I update the price sheet.
Is there a way to either:
- Make excel leave the old vlookup value in the sales sheet once a sale is made and NOT update the price on later sales (doubt this is possible)
- OR, make the macro not overwrite old data.
I really need it to leave the sales page alone too, and my old option was to have the world’s longest nested IF statement. That won’t work for the people who will use this workbook…they are basic users w/o ability to change something that complicated.
How can I make the sales entry page be “final’?

 

[Fluff]

Hiya
Are you looping through the entire sales sheet each time the macro runs?
If so, how about storing the number of used rows within the sales sheet each time, & then use that for the loop eg

    UsdRws = Range("A" & Rows.Count).End(xlUp).Row
    
    For i = Range("A1").Value To UsdRws
'    ...
'    ...
    Next i


    Range("A1") = Range("A1") + UsdRws

HTH

 

[amandabstewart]

Can you further explain your reply? It would look through the price list each time...for example, we would do data entry of the sales (say, 10 entries) and then run the macro...which should copy the info over to the individual's page (which it does correctly), but, if I change prices and re-run it, it overwrites the old sales $ the individual earned. When we enter the data, it would likely be 10 entries belonging to 10 different individuals.
Does that clarify? I'm still somewhat new to VBA...trying to learn


Would I copy the code you provided at the end of current macro?

 

[Fluff]

Hiya
Kindly disregard the code I posted before, not sure what I was doing.
Best bet is if you could post your code & I'll show you how to change it.