MACRO TO SUM DATA!

[jay dean]

In SheetA , Range(D443) contains dates. Column E through N contain
data.

I need a macro that will do the following:
1. Go through the date column, if the month and year of each date in
Range(D443)is same as the current month and current year, then for
each column in the data range from column E through N, SUM up all the
corresponding data and put the total in row 44 of the column.

(Simply put - calculate the month-to-date into row 44 for the data in
columns E through N based on the dates in column D)

2. THEN offset the date range(D443) 43 ROWS DOWN and repeat the same
calculation for its data range in columns E through N as above.
--It should continue looping down the sheet until the offset date range
in column D contains no dates.

Any help would be appreciated. I have fought with this so by myself for
so long I am getting a headache. thanks.

Jay Dean

 

[Guest]

Hi Jay

No need to stress, I hope this code helps you out. The only odd thing that you should note would be that I assumed that each block of data is 43 rows, so the 'subtotals' are on lines 44, 88, 132 etc. However, in your 1st point, the data was only 40 rows...odd

Anyhoo, give this a whirl

-----------------------------------------------------------------------------
Sub YTD_Tot(

Dim Today As Lon
Dim RowCount As Lon
Dim i As Integer ' Subtotal Row Multipl
Dim j As Integer ' Column to su
Dim SubTotRow As Long ' Subtotal Ro

Today = Dat
RowCount = ActiveSheet.UsedRange.Rows.Coun
i =

D

SubTotRow = 44 * i ' Each Subtotal comes after 43 lines of data (so subtotals i
' rows 44, 88, 132, et


For j = 5 To 1

Cells(SubTotRow, j).Value = Application.WorksheetFunction.SumIf(Range(Cells(SubTotRow - 43, 4), Cells(SubTotRow - 1, 4)), "<=" & Today, Range(Cells(SubTotRow - 43, j), Cells(SubTotRow - 1, j))

Next

i = i + 1 ' Next Subtotal Ro

Loop Until SubTotRow > RowCoun


End Su
-----------------------------------------------------------------------------

Please let me know how you go

See ya

SuperJas.