VBA for creating a chart.

Hello folks, I am new to this forum. I have spent the last week and a half creating a macro for the 3000 excel files that I have. They contain 1 years work of data for 3000 stocks. I am trying to move on to the next step but I am lost. My wife is already angry at me for spending so much time at night getting to where i am now. So it is time to ask for help to move this next step along faster.

What I am trying to do now is take all data in column D, Column E, and Column F that corresponds to all of the "Monday's" which is in column G labeled "weekday" and take an absolute average of all the values. In other words Average of all Column D in one cell, average of all E, in the cell next to it and the average of all F in the cell next to E. Then I want to move on to all of the Tuesdays which are in different rows in column G and do the same thing with columns D E F that correspond to "Tuesdays". I have a years worth of Monday thru Friday that I am trying to create a chart from.

The X-axis is going to contain the days of the week and the y axis will contain the average D, average E, and average F for each day of the week.

Thanks to this forum I already know how to open files in a folder to modify. I am just looking for a starter macro to get the general idea of where to start. The columns are hypothetical and I will adjust as needed.

Thanks in advance

mrforeman,

I always start my code with the macro recorder. This will record all your actions and even though its not perfect it will set you in the right path. My advice go ahead and record what you want to do and then port the code here. It would give us more tangible information to work with. Also if you can attach a copy of your workbook so it can be tested and debugged.


Thanks a lot

Here is the sheet with the recorded macro from below

Please Login or Register  to view this content.

Hi mrforeman,

I am sorry for the delay.

try this out and tell me how it goes.

Please Login or Register  to view this content.

I am working on making some changes to it and try to cut down on some things. It looked fine as it was some things were off. I am working on some filters so that if I have lets say 2 years worth of data it will filter for the day of the week and use all of the colums instead of the fixes ones I have now.

Thanks for the help, it have me an idea on where to start. I will post again when I have more questions "cause I know I will" and after I get some of the filters in place.


Thanks again

I am glad I helped

I've been looking for some days and I haven't figured out if there is a way to use ABS with an array in a macro. I used the worksheet function subtotal of a range and get a number, but abs ofcourse gives me a error because of the string. I know there has to be a workaround but I just can't find out. Any assistance would be great.