Thread: need to make an excel macro that updates prices from another worksheet

Ok I have a pricelist that updates our engine parts list for MYOB via the internet using a macro, this price list contains 28,331 individual parts.
I also have a mysql database of a select 713 of these parts (these are the most common parts that we want to catalogue on our website).
What I would like to do is have a macro update the prices of these 713 parts from the worksheet containing the 28,331 parts using the item number as a reference.

For example, I think the code needs to take these steps:

1. Look at worksheet 'mysql' , cell 'E1' (Column E being the item number column on the mysql worksheet containing the 713 parts).

2. Store the contents of that cell in a variable (for argument sakes lets call this variable 'N').

3. Search worksheet 'updated' , column 'A' (Column A being the item number column on the updated price list for MYOB containing the full 28,331 parts) for variable 'N'.

4. On whichever row this is found I need it to look at column 'C' (Column C being the price column).

5. Store the contents of that cell in another variable (lets call this variable 'P').

6. On worksheet 'mysql' replace row '1' column 'F' (Column F being the price column) with the price in stored variable 'P'.

7. Repeat these steps for the next row and so forth until all 713 parts on the list have been updated.


I know that is probably not how an excel macro even operates, I have never worked with them before but its the way I found easiest to explain step by step what I require the macro to accomplish.

I would really appreciate any help, thanks.

I had a look at how to do VLOOKUPs and I couldn't make it work for me.
I need to actually instruct excel to search the 'myob' worksheet in column 'A' (Item Number) for whatever is in cell 'E?' on the 'mysql' worksheet (Item number).

When it finds it I then need it to copy whatever is in column 'C' (Price) on that same row to column 'F?' back on the 'mysql' worksheet.

It's quite hard for me to explain, I hope you understand.
I'm not sure how I could make a VLOOKUP do this.
The two worksheets are not mirrors of eachother as the mysql worksheet only has a small selection of the items that are on the myob worksheet.
I would like to be able to add more items to the myob worksheet and it still work.
And would rather not have to create a new VLOOKUP for each item.

This is why I thought a macro could do this.
There are already macros in place in this document to update the myob database from a tab delmitered text file from a supplier website and then export the updated myob database as a text file for re-importing back into MYOB. I would like to integrate into that macro the code to update those items on the mysql worksheet from that updated myob worksheet and automatically export that worksheet as a Comma Seperated Value text file for importing to my MySQL server.

I know that is probably not how an excel macro even operates, I have never worked with them before but its the way I found easiest to explain step by step what I require the macro to accomplish.

You explained it pretty good and the way you described it would work, But a good programmer would still use the VLookup in the macro, which would be much faster than doing a search one by one of each of the 28000+ rows. And doing it 700 times could easily make it too slow.

And using the VLookup in a formula instead of a macro would even be faster, it would actually be almost instantaneous, and each price would be automatically updated when the data on 'updated' changes, without having to run a macro.

So try this first. If it works for you, it will be faster and easier to maintain.
In cell F1 : =VLookup(E1,'updated'!A:C,3,FALSE)
Assuming that it shows you the correct price:
copy F1 all the way to the bottom of the data.

If you want to be able to add rows onto mysql data and have Excel automatically put the formula into column F for you, that can be done. It can even be set up so that if a price is changed anywhere on the sheet, it automatically creates a comma delimited text file.

You need to attach an example workbook. Without seeing the layout I would suggest the >Find method in VBA,not VLOOKUP

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save