Hi,
I'm trying to load the CSV version of a monthly report and insert SUBTOTALS and a Grand Total in palces that will obviously change each month. The Data Import and removal of unneeded headers work fine but for now I'm adding the SUBTOTAL() function manually. The module below adds the single and double lines when I comment out the "ActiveCell.Value =" but crashes when I remove the comment-mark. I suppose the answer would allow me to insert whatever formula I need (=STR(), =MID(), etc.
As an additional point, I'm wondering if there is a way to read the current column letter and include that in the assignment at run-time. The columns (G and H in this case) won't change but I think it would would be more elegant if they weren't hard-coded. Thanks, as always
I'm trying to load the CSV version of a monthly report and insert SUBTOTALS and a Grand Total in palces that will obviously change each month. The Data Import and removal of unneeded headers work fine but for now I'm adding the SUBTOTAL() function manually. The module below adds the single and double lines when I comment out the "ActiveCell.Value =" but crashes when I remove the comment-mark. I suppose the answer would allow me to insert whatever formula I need (=STR(), =MID(), etc.
As an additional point, I'm wondering if there is a way to read the current column letter and include that in the assignment at run-time. The columns (G and H in this case) won't change but I think it would would be more elegant if they weren't hard-coded. Thanks, as always
Code:
Sub Add_ChkSum()
'
' Add_ChkSum Macro
' Macro recorded 9/15/2006 by Jim
'
Range("G609").Select ' 2 Right of 'EOF'
ActiveCell.Value = "=SubTotal(G" & Str(iTopDataRow) & ":G" & _
Str(ActiveCell.Row - 1) & ")"
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Range("H609").Select
End Sub