Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO SUM DATA!
In SheetA , Range(D4:D43) 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(D4:D43)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(D4:D43) 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO TO SUM DATA!
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(Su bTotRow - 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy data when data is detected in another column(s). | Excel Worksheet Functions | |||
Write Macro to Fix Data Alignment (Data dump from Crystal to Excel | Excel Discussion (Misc queries) | |||
Excel OLE data in PPT reverting to OLD data after macro updation | Charts and Charting in Excel | |||
Macro to move data to different column based on data in another co | Excel Discussion (Misc queries) | |||
enter data in cell which will start macro to move data to sheet2 | Excel Discussion (Misc queries) |