Traverse matrix and loop help

[amajor99]

Good Morning,

Can anyone help with a complicated VBA?

I need the VBA to go into two tabs (flat fee and phase tier) in each excel workbook and pull the data out (they will sit in a specific folder).

I have coded the data into two separate arrays- A9:A80 and then D4:GJ4 for each tab, so basically I need to loop and pull the file in by row and by column. See attached workbooks. There will be about 60 of the excel files, so that’s why I need a macro to help.

I think the result of the macro would look like this: so it would pull in every data field in all of the excel tabs for D4:GJ4 and then cross reference to the data array A9:A80 (in this case city/state) and populate.

Is this even a possibility?

I attached a sample workbook and then a picture of what I think the end goal is.end goal.png

[Bernie Deitrick]

Where do you get the 1200, 600, 800... etc. values? There was nothing in the workbook....

[amajor99]

Hi,

I just typed those in quickly as an example of how I'd like the macro to return data. I can see the data in the excel book I attached, not sure why it wouldn't work for you.

[Bernie Deitrick]

I see the labels and column headers, but everything else is blank. So, I'm not sure what you want done with those...

[amajor99]

Hi there, this is a bid form. So it will be sent out to Law Firms to bid on it. They will fill in pricing in the blank cells where applicable. I am looking to write a macro which will summarize the data in the example above. So basically I would need the macro to loop through the rows/columns specified to return all of the values in a large number of workbooks that will be sent out and filled in.

[Bernie Deitrick]

What would you want entered into cells if two or more files have the same cell filled in but with different values?

Instead of the format that you are suggesting, it might be better to create a database from only the filled values (Law Firm Name, Row label, column label, value, and other information if needed) that could then be filtered to show a value of interest.

[amajor99]

Hi Bernie, two or more files would never have the same cell filled in, since each file will be a different law firm. The unique identifier would hold the law firm name (the first 5 letters) the file type, and the pricing type.

[amajor99]

So where it says "Test2" that is actually going to be the law firm name. In each excel workbook, the name will be unique.

[Bernie Deitrick]

Try the macro below - copy the code into a standard codemodule in an otherwise blank workbook, and run the macro "GetDataFromBidBook" and choose one of your bid files when prompted. Then try it a second time, choosing a different workbook. If the code works, it can be modified to allow the selection of all your files in one go instead of one at a time.

The code creates a database that can be filtered by county, to show the different values bid for that county. The second column can be filtered using the "Contains" text option to look for a specific service.

Please Login or Register  to view this content.

[amajor99]

Thank you for the code! I cannot get it to work. I filled out a dummy bid sheet with numbers. It's called test 2. then the macro is in the macro workbook. Can you take a peek?

[Bernie Deitrick]

The workbook that you posted the first time had only two sheets, and the workbook that you are using had six worksheets (three of which were hidden). And it had formulas in the range of cells where the users would enter their values - my code was looking for constants, not formulas. Macro code is specific enough that things like that matter. So, anyway, try this version, which looks for two "Pricing" sheets, and converts everything to values before proceeding (but does not over-write the original workbook, so it won't matter):

Please Login or Register  to view this content.

[amajor99]

OMG that worked! Thank you so much!!!!

[Bernie Deitrick]

Great! Thanks for letting me know that my code worked for you.

[amajor99]

Hi Bernie- I have to make some changes to format the data differently.

Quick overview, I need to go into each "firm name" excel file within a folder and pull out data from the flat fee tab and the phase tier tab. Some data sets need to be transposed and some do not. I would like to pull the "Flat fee data into the flat fee tab on the input file, and the phase tier data into the phase tier tab on the input file) When this is done for one firm, I would like the file to close and then open the next firm and do the same thing.


1. Logic to pull several excel files from a specific folder/directory extract data and then close and move to the next. Let's call the folder/directory "Wisconsin" so I can easily spot and change it to the actual directory in the code.

2. Logic to open file named "Firm A" (which sits in the folder Wisconsin) and go into the tab named "Fixed Fee Pricing" and pull in the Firm name in cell B2 (return this firm name to the input file in the flat fee tab cell C1).

3.Logic to pull the "Fixed Fee Pricing" data from A8:A79 and return it to input tab "Flat Fee" A2 and going down.

4.Logic to pull the "Fixed Fee Pricing" data from D4 to W4 and return it to input tab "Flat Fee" B2 and going down.

5. Logic to pull the "Fixed Fee Pricing" data from D8:W92 and match up by column A and row 4 (so matching it by county/symbol) and then transpose it and return to input tab C2 and down.

so basically I want to lookup the county state, the symbol type and the fee and return it in the three columns in the input file by firm name (firm names to populate C1,D1,E1, etc.... ) with the pricing, county state and symbol going down.

Same thing for the Phase tier tab.


Then I want Firm A to close, and Firm B to open and populate all of the data in D1 going down. - Only the price though, since the county state and input file will have already been populated.





I am attaching the "Wisconsin" file that I would like to have the macro in, and then the Firm A and Firm B.

[Bernie Deitrick]

I have no idea what you want at the end of all this: the range D8:W92 is completely filled, so why do you only want to fill column C from that range? Could you modify your input file to show what data you want in columns A to C based on your file Firm A.xlsx?

[amajor99]

Sorry I am really struggling with the correct language.

Ok, I updated the input file, "Phase Tier" tab.

So, in the input workbook, I will need column A to populate every county/state from Firm A "Phase Tier Pricing". There will need to be 180 of each county in Column A in the input tab since there are 180 unique types of pricing. Basically, each county has 180 different types of pricing.

Then, I need to pull the file types from Firm A (row 4) and transpose to input file column B. These will need to repeat for each of the 72 counties. So Esentially, there should be 12,960 rows going down.

Lastly, I need the macro to open every Firm file in the main folder and dump the name into the input file (column C1, D1, E1, F1) and then look up the price according to county and file type and return the rice in column C. Then the macro will close Firm A, and open firm B and populate the firm name in D1 and then return the pricing in column D.

Does that make more sense?

Then, I will need to This is a one time dump, once Firm a is populated, firm B and C and D etc... will all have the same counties. So I will only need the macro to pull in the appropriate prices.

I would like column B in the input file to populate the file types (WC_T3_P1)

[Bernie Deitrick]

Try this in your input.xlsm. Select the file(s) of interest when prompted

Please Login or Register  to view this content.

[amajor99]

Wow, that is amazing. Thank you so much for your help!!!

[Bernie Deitrick]

You're welcome! Have fun transforming your data!

[amajor99]

Hi- I have a huge favor to ask. I had to add a section to one of my tabs and now the macro is not working. Can you review?