Add Subtotals after inserting row
Thanks Dave....Can anyone help please....
"Dave Peterson" wrote:
Maybe someone else will jump in.
Good luck.
Kam wrote:
Hi I have tried to explain in simple way....Please Please give me macro which
can do that...please....please...
here is my problem ...
I have a worksheet with data in it .. in column"A" I have a date value ...
what i would like to do .. is
1) insert a row after a certain criteria is met ie.
Column A Column H
row 1 = 07/07/2004 100
row 2 = 23/07/2004 50
row 3 = 13/06/2005 30
row 4 = 01/09/2005 20
row 5 = 27/12/2005 10
row 6 = 02/02/2006 05
row 7 = 14/02/2006 07
I would want to insert a row between row 2 and 3 then 5 & 6...&...So
on...when year in dates change
2) I would like to total the numbers in column H above the column that I
just inserted
so basically trying to do a grouping with totals.
any help would be appreciated.
"Kam" wrote:
Yes I know that it is possible my using by recording macros but it won't be
suceessful every time bcoz blanks rows varies...
"Kam" wrote:
Sub EmptyRow()
Dim cou As Integer, MPStr As String, MPString As String
For cou = 1 To ActiveSheet.UsedRange.Rows.Count - 1
MPStr = Format(ActiveSheet.Cells(cou, 1), "YY")
MPString = Format(ActiveSheet.Cells(cou + 1, 1), "YY")
If Not Val(MPString) = Val(MPStr) Then
ActiveSheet.Rows(Trim(Str((cou + 1)))).Insert
cou = cou + 1
End If
Next
End Sub
I have above macro script which will add blank rows when year changes....But
need some more help to add subtotals after inserting rows.
Please see below is the data which I have in my excel sheet.
Invoice Date USD Amount GBP Amount
07-Jul-04 0.00 -545.63
07-Jul-04 -5,474.00 -2,991.54
23-Jul-04 -7,333.89 -3,962.19
13-Jun-05 -1,583.00 -843.27
01-Sep-05 -3,858.00 -2,158.94
16-Nov-05 1,104.00 632.74
01-Dec-05 -2,754.00 848.00
27-Dec-05 -1,778.00 -1,015.66
30-Dec-05 -581.00 -328.93
02-Feb-06 3,115.76 1,763.51
14-Feb-06 2,534.76 -956.54
16-Mar-06 2,416.61 1,389.52
16-Mar-06 -1,016.67 -584.58
17-Mar-06 -1,399.92 -804.94
29-Mar-07 1,159.00 661.91
30-Mar-07 2,439.57 1,388.95
01-Apr-07 6,357.54 3,660.16
Please advise if you require any further info.
Best Regards,
Kam.
--
Dave Peterson
|