Thread: Creating an Add-in / Macro

Hi everyone,

I was curious about getting some help for excel 2007. I have recently found a job that requires some work in Excel to speed things up (significantly) if I can find a way to do it. The problem is it requires some complex procedures and scripts. Complex for me anyway, as I have no clue on how to program.

Basically I need an addin that can: Clean cells, trim cells, add prefixes/suffixes to cells, concatenate cells. Thats the simple formulas now to what I really need to be able to do. I'll try to paint a picture that's easy to understand.

Ok so I have two different excel files. I need to basically copy information from one file to the other according to reference information. So for example in column "A" I have the code ABC123, and in column "E" I have 576. Then in the second excel file I also have ABC123 in column A but the number is different, say 456 (and in a different column, say "R" for example). I need the script file to find exact information in one file according to it's "code" and change the relevant information in the other excel file.

If a code is found and a change is made, it highlights the row green. If a code is not found, it highlights red and no change is made. If a code is found but the numbers are the same, it highlights yellow.

I hope this all makes sense, it's much easier to understand than to explain. It just saves a lot of manual data entry. I really hope someone can help. Thank you!

Garuda

Garuda,

Congratulations on finding the new job. It's always exciting, and it always presents unique challenges. I hope you enjoy.

Am I understand that what you want the form to do is find the information (123) in workbook 1, and then change the corresponding information (456) in workbook 2?

Is the information being taken from workbook 1 always in the same column? Will it always be going into the same column in workbook 2? What criteria are you using to find the information in workbook 1 and workbook 2? Is it based on another column with something along the lines of a matching category name?

Thanks for the reply, sorry it was not clear enough. I've attached an image to make things a little clearer and I will give an explanation to explain each situation.

NOTE: Document 2 will be the information that will update Document 1. The only information that will be updated with this script is Sale Price Column in Document 1.

NOTE 2: Correlating Product Codes will not be on the same Row in real world documents. Product Codes may not correlate either. For example Doc 1 product codes are in column A and Doc 2 product codes may be in column C.

1. Product Code matches in both Documents and the Sale Price is equal in both documents. This means there is no change and row 3 in Document 1 and 2 will turn YELLOW.

2. Product Code does not match so there is no information to update in Document 1. Row 5 in Document 2 would turn RED. There is no Row in Document 1 to change color, as it does not exist.

3. Product Code matches and would therefore update Sale Price in Document 1 (47.38) with Sale Price from Document 2 (43). It would be ideal if it could update the prices and keep the prefix "AU/". Rows 9, 10, 11 and 12 would all turn GREEN.

4. This is just an example of what an updated price would look like. In this example, Sale Prices from Document 2's "F" column are updating prices in Document 1's "C" column.


I hope this is a little clearer for anyone who might like to help. The thing is, I don't know how to code or use I think it's called VBA? I really don't know where to start, so any help would be great. Thanks in advance.

Garuda

Anyone have any ideas? Maybe a little involved for this forum? If so, maybe someone could head me in the right direction where I might be able to find a solution.

Thanks
Garuda