Trying to put results from Array back into Table after running through If Else If Loop

Hi,

I will preface this by saying that I have a rudimentary knowledge of excel/VBA macros that usually is enough to accomplish what I try to do, it might not be the most efficient code or even the best way of doing something but I usually can get things to work as I want them.

The attached Workbook will show a very basic worksheet with basic company and contract data in columns A to G, columns I,K,M,O are the columns I am trying to populate using the data found in the rest of the columns (Q through the end of the Tablerow). These columns need to be ran through a loop to collect and sum up all Invoices in current row for each Quarter (Quarter dates are specified in code)

Image Snipe.PNG

The data consists of a invoice date and a invoice total amount. My original thought process was to load the table data all into an array, breakdown each set of invoice data (i.e. Data and Total) using Redim and size of the array. Then setup a loop to go through and collect the appropriate data and assigned the value from the DataArray position for the appropriate variable. This is where I am sure things could be done more efficiently as I then run all these variables through IF statements to check which quarter each invoice date falls under and add to another variable to collect. I figured once all these were collected, I could spit it back out into the current row column I,K,M,O. and the have the loop go through the rest of the rows and do the same thing. This is where I ran into trouble as I couldn't figure out how to a) Put the results back into current row b) loop back start the process all over again. The next i in my code seems to start the process over but without clearing the variables it overwrites the ones that were from the first time through the loop and the results are lost.


Any pointers or assistance would be appreciated. Excel file is attached.

Thanks
Dan

At first glance i would say create a pivot table from the data you are interested in (do it through VBA) and then reference the specific data from within the Pivot table - you have essentially had the Pivot table do all the hard work of defining what falls into each quarter/period etc.

Craig