Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Macro
I have this code that detects dates in Column (A). At the end of the workweek when the macro runs it inserts a row with "Weekly Totals" and sums up certain columns. The problem I have is if there is no date after a weeks end it will not insert a row with "Weekly Subtotal". Below is the code.........PLEASE HELP! Sub WeeklySubtotal() Dim rngCell As Excel.Range Dim rngSum As Excel.Range Dim i As Long Dim lngR As Long Set rngCell = Range("A9") lngR = rngCell.Row Do If IsDate(rngCell) And IsDate(rngCell(2, 1)) Then If Weekday(rngCell(2, 1).Value) < Weekday(rngCell.Value) Then rngCell(2, 1).EntireRow.Insert rngCell(2, 1).Value = "Weekly Subtotal" For i = 4 To 7 Set rngSum = Range(rngCell(1, i), Cells(lngR, i)) rngCell(2, i).Value = Application.Sum(rngSum) Next i Set rngCell = rngCell(3, 1) lngR = rngCell.Row Else Set rngCell = rngCell(2, 1) lngR = rngCell.Row End If Else Set rngCell = rngCell(2, 1) lngR = rngCell.Row End If Loop Until Len(rngCell.Value) = 0 End Sub -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=522250 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Macro
Dim bAdd as Boolean
bAdd = False If IsDate(rngCell) and ( IsDate(rngCell(2, 1)) or isempty(rngCell(2,1))) Then if not isempty(rngCell(2,1)) then bAdd = _ Weekday(rngCell(2, 1).Value) < Weekday(rngCell.Value) else bAdd = True end if If bAdd Then rngCell(2, 1).EntireRow.Insert rngCell(2, 1).Value = "Weekly Subtotal" -- Regards, Tom Ogilvy "parteegolfer" wrote: I have this code that detects dates in Column (A). At the end of the workweek when the macro runs it inserts a row with "Weekly Totals" and sums up certain columns. The problem I have is if there is no date after a weeks end it will not insert a row with "Weekly Subtotal". Below is the code.........PLEASE HELP! Sub WeeklySubtotal() Dim rngCell As Excel.Range Dim rngSum As Excel.Range Dim i As Long Dim lngR As Long Set rngCell = Range("A9") lngR = rngCell.Row Do If IsDate(rngCell) And IsDate(rngCell(2, 1)) Then If Weekday(rngCell(2, 1).Value) < Weekday(rngCell.Value) Then rngCell(2, 1).EntireRow.Insert rngCell(2, 1).Value = "Weekly Subtotal" For i = 4 To 7 Set rngSum = Range(rngCell(1, i), Cells(lngR, i)) rngCell(2, i).Value = Application.Sum(rngSum) Next i Set rngCell = rngCell(3, 1) lngR = rngCell.Row Else Set rngCell = rngCell(2, 1) lngR = rngCell.Row End If Else Set rngCell = rngCell(2, 1) lngR = rngCell.Row End If Loop Until Len(rngCell.Value) = 0 End Sub -- parteegolfer ------------------------------------------------------------------------ parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951 View this thread: http://www.excelforum.com/showthread...hreadid=522250 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |