Help with macro formula and variable
OK, here is what I am doing...
I am opening up a csv file, forcing the B-column to be text. I copy all the data from there into another excel sheet. I use a macro to turn that data into a pivot table. This table varies in rows and columns depending on the amount of data. I want to put an average formula at the bottom of each column that only averages the above cells that are in a certain value range (example: average everything between 5 and 45). I know this has to be done with an array formula. The problem I have is getting the macro to put the formula in the sheet. I need the macro to be able to change the range that it puts in the formula to suit the number of rows(example: sometimes the range will be B5:B30 other times B5:B45). I already have the values 30 or 45 from the example counted in the macro, can I use them as a variable in the formula somehow? Once I get the macro to do this part correctly then I need to be able to fill across the formula to the end of the columns. After that, I need to put in a rank formula under the average formula to rank all of the averages and fill that across. It would be nice to have the averages/rank update if I need to turn off any columns in the pivot table. Maybe I should do that with a seperate macro? I realize this is a lot to ask for help on but if I could just figure out how to have the macro put a different range in the formula that would be a huge help. Thanks to everyone for all of their help. Dan |
All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com