Advanced VBA/Macro - Search for particular word in one excel workbook and pull data into another workbook

[ekfasy]

Hi,

I'm currently trying to create a vba macro to search in one excel workbook for a particular word, and then pull the data for that into a different excel workbook.

Some background for my project: I have different quotes from vendors (all of which are in different formats), and I have to automate the process of taking the data from a vendor quote and putting it into my company's quote format to give to our customers. I need the vba macro to search for things like Quote Name or Customer Name, Quote ID, Quote Expiration Date, etc. I also need to pull in the different product numbers that vendors are quoting to us. These product numbers and descriptions will differ greatly from quote to quote, so I also need a way for the vba macro to know what a vendor product number and description look like or which header it is under, and pull that data into our quote in the appropriate place.

Any suggestions/help would be greatly appreciated, as I am new to vba and macros!

Thank you in advance,
Erinn

 

[Roderick_E]

How consistent are the formats of the vendor quotes? Are the headers on the same row for example? What you are asking can be done but would require some fancy VBA coding that;

1) Determines the header row of the vendor quote
2) Determines the column of the header row that contains the header string, such as Quote Name

This assumes the value/data you want is then on the very next row after the header row.

 

[ekfasy]

Hi Roderick_E,

There is no consistency between different vendors' formats, but the vendors themselves keep a consistent format between each of their own quotes. I had an idea for there to be a drop down box or a field for the user to choose which vendor they are using, and then depending on that answer a certain vba code will perform. Any ideas on this?

Also, for things such as Customer Name, Quote Number, etc. the data that needs to be pulled is in the adjacent column. For things like Product Number, Product Description, Price, etc. then there will be a header and the data for each will be in the following rows.

I figured the coding would be complex, as I have been reading a vba programming book and still have not seen anything complex enough for me to use to execute this.

I don't know how to attach an excel file on here, but if someone explains how to then I can attach a quick example of one vendor quote format, and our quote format.

Any help, even if small, would be so much appreciated! I have to get this done for work as quickly as possible!!!!

Thanks,
Erinn

 

[Roderick_E]

On a hidden sheet, you could store the configurations for where each vendor's data is on their respective sheets; header rows, columns and such. Then when the user executes a search you could refer to these configurations. You could have a dropdown like you mentioned or you could determine if each vendor workbook has something that makes it unique and have your code determine which vendor is being used. In this way, you could loop through multiple, different vendor sheets and yet gather the data and put into the format you want. Again, this all requires some fancy coding as well as setting up a hidden configurations tab.

 

[ekfasy]

How hard is it to set up a configuration tab? I've never done that before...

 

[Roderick_E]

It's just a tab with things like a cell that says Header Row and perhaps a 5 under that to indicate row 5 is the header row and perhaps a cell that says Quote Name and a column letter under that to indicate this is where the Quote name is found on the specific vendor sheet.

 

[ekfasy]

Thank you Roderick_E!!!