Visual Basic Forum for Visual Basic Programmers VB

[sirgeoff]

I'm trying to subtotal at the bottom of column a,b,c,d(at line insert). I'm having difficulty identifying the range, since it is changing with n. Any idea?


For n = 3 To 999
Cells(n, 1).Select
If (Cells(n, 1)) = "" Then


Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

different_month = DateDiff("m", CDate(Cells(n, 5).Value), CDate(Cells(n - 1, 5).Value))

Else: GoTo 12
End If

If different_month <> 0 Then

Selection.Insert Shift:=xlDown
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With

Cells(n, 6).Select
ActiveCell.FormulaR1C1 = "Monthly Total"


ActiveSheet.Cells(n, 1).Formula = "=Sum(a1:a5)"

Exit For
End If
12 Next n
e.i.


4.0 0.5 0.1 0.0 2/28/2004
4.0 0.5 0.1 0.0 3/1/2004

4.0 0.5 0.1 0.0 3/2/2004

..............................................................................
..............................................................................
..............................................................................
..............................................................................
3.2 0.5 0.1 0.0 3/31/2004
________________________________________Monthly total
4.0 0.5 0.1 0.0 4/1/2004

Therefore I would need to datediff some how back to February 28 to subtotal the columns for March. I just can't grasp how to get the range to read correctly.