Joined: 06 Oct 2009
|Posted: Tue Oct 06, 2009 4:16 am Post subject: Using a macro to compare and change values in Calc
|Hi everyone, thanks a lot for taking the time to read this post. I need your help and advice and I thought this would be a good place to start.
I have two spreadsheets (both comma delimited files) - one with a current list of prices for products and another with the new prices for the products. The structure of the spreadsheets differ but the same product/model numbers are used in both. What I would like to do is get the product number from one sheet, find it in the other sheet, get the corresponding price for that product and paste it back into sheet one.
What I would like to know is if it is possible to build a macro that does this (in a rough example):
Macro starts on row 1/sheet 1 and copies the first cell value (the product/model number).
Macro then moves to sheet 2 and searches for that cell value.
If found the macro copies cell X from the row corresponding to the matching p/m number. (i.e. the new price)
Then the macro goes back to the original sheet and changes cell Y with the info from s2/cX. (replacing the old price with the new one from the new sheet).
Move on to next row in sheet 2.
If a matching p/m value isn't found on sheet 2, then the macro will add a character in another cell on sheet 1 - indicating once the process has finished which ones have not been updated and can be easily seen by the user of the spreadsheet.
If it's a lot of work please tell me, I don't want to take advantage of anyone but I would like to know if this is feasible. I keep getting a nagging feeling in my mind there's an easier way of doing this than by hand, but I simply lack the knowledge to build a way.
Thanks in advance