Compare columns, replace matching number with reference number and fill down random amount

Hi!

I'm not sure how to explain what I need bc I don't know if a simple formula will do it or if I will need to run a Macro. I have searched and searched and tried plugging in different formulas and macros myself but nothing works. I have four columns of data: Old prices, New prices, The Current Price List and a column with the new prices that need to be filled down a random amount of spaces to match the format of the Current Price List. (In the example they are filled down a set amount, but in the actual worksheet the amount is random.) I know there is a search or find and replace or maybe even a match formula, but I cannot figure it out. I have attached an example file of what I need to do here along with instructions next to the columns.
Example.xlsm

Thank you so much in advance for your help!!
Michelle

If I've understood what you need, perhaps something like the following will work.

Please Login or Register  to view this content.

That works ALMOST perfectly, and it would have been perfect undoubtedly if I had remembered to mention the following. :/

Some of the prices in the real current price list are wrong. They don't match up with any number in the Old Price list. When the code runs into these numbers in the Current price list, I need it to make them stand out (either by highlighting them or turning them to 0 or something) so when the code is finished running, I can scan the database and see what I need to go in and correct.

Thank you so much for your help. I have a TON of databases that will need to run this code. Also, is there a website I can go to that I can plug in an excel code and it will break down and explain each part for me?

Thank you again!
Michelle

Perhaps something like this?

Please Login or Register  to view this content.

Also, is there a website I can go to that I can plug in an excel code and it will break down and explain each part for me?

This one! :-)

Also, one can learn a lot by stepping through the code one line at a time.

I want to hug you!!!!!!!!!!!!!!!!!!!!!!!!!!!!

DogWhosAwesomeYouAre.jpg

I'll go through one line at a time and try to figure it out myself. I'll learn more that way.

Or you can ask. What do you not understand? (There are no stupid questions!)

Haha, that's the thing. I don't understand most of it. I guess what would help the most is knowing how to set up a code, or knowing where I can find out how. Excel Codes For Dummies? I can usually figure out how to plug in formulas, but for a complicated thing that requires code, I get lost because coding is lost on me.

(1) You need a Microsoft Office Excel Macro-Enabled Worksheet (.xlsm). You can create one by saving an excel file "Save as" Macro-Enabled Worksheet. (Your Example Worksheet appeared to be an Excel Macro-Enabled Worksheet.)

(2) Your Macro-Enabled Worksheet needs to be in a folder which you can designate was macro friendly. The way you do this: click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations. Then you add your folder (where you have your Excel Macro-Enabled Worksheet) as a trusted location.

(3) With your Excel Macro-Enabled Worksheet go to the menu item "Developer" -> Visual Basic.

(4) In the VBE (Visual Basic Editor), go to Tools -> Options and click the "Editor" tab and check the box next to "Require Variable Declarations" (if not checked). (This is not necessary, but good practice. Only needs to be done once.)

(5) From the menu: Insert -> Module; a module should open to the right with "Option Explicit" (in blue type).

(6) Copy and paste the following under "Option Explicit":

Please Login or Register  to view this content.

(7a) With your "Example" worksheet active, go to the menu: "Developer" -> Macros. I popup box should now show the macro: "UpDatePrices", click and run.

(7b) Or with your "Example" worksheet active, go to the menu: "Developer" -> Visual Basic, click inside the macro in the module and press F8. Repeat F8 while you step through the code. (Play around with it.)

If you get stuck on a step, just ask.

Oh man, I should have been more clear. I ran the code as soon as I got it and it worked (that's why I said I could hug you, haha!)-I can do that, but it's knowing how to build one. For instance-you read my problem and knew how to write out the code to fix it. (The formatting of it, I mean.)How do you know how to do that? Is there a standard way to format it? Sorry you spent all that time typing out the last response, but I appreciate it.

That's okay, I often misunderstand what people write. We all have our strengths and weaknesses. As for me, I tend to understand VBA better than I understand Excel. I'll often write code only to see someone write something better because they understand how Excel works. I like writing code, because it is like a puzzle (not that dissimilar to chess; I'm a High School chess coach). I'm always looking for better ways to write code, but also better ways to explain how to write code. Actually, the bit of code I wrote for you was very basic. Not much to it. I read and re-read a half dozen or more books on Excel VBA. And then I write and re-write code in order to see how it works. I find reading this forum to always be an education. It amazes me what people know. Unfortunately, my knowledge is only rudimentary, I've only been able to work at coding on and off throughout the years, never had the time to be consistent.

I love the puzzle part of it, too! Thank you again for your quick response and for all of your assistance. I'm so glad that forums like this (and people like you) are out there.