Log In
Register
Home
November 8, 2018
Offline
I'm trying to write a macro. Where Columns D:F have an auto sum form the row that has "totals" in column A. The number of rows varies each time. But the sum need to go into the highlighted rows. I manually enter numbers for jeff and ivan so the sum needs to be able to include those cells.
I also want it to double underline the sum and single underline the row above.
VIP
Trusted Members
June 25, 2016
Offline
Hi Julie
Give this a try.
Sub AddSum()
Dim rw As Long
'Find the row with the word Totals in column A
rw = Application.Match("Totals", Range("A:A"))
With Range("D" & rw & ":F" & rw)
.Formula = "=SUM(D2:D" & rw - 1 & ")"
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
End With
End Sub
Sunny
November 8, 2018
Offline
That worked except it puts the formula in the row titled Equipment and not the Totals row. Sometimes it puts it in the Totals row but usually int the Equipment row.
VIP
Trusted Members
June 25, 2016
Offline
My mistake 
.
I left out one argument in the MATCH. I tested it without your rows 12 and 13.
Sub AddSum()
Dim rw As Long
'Find the row with the word Totals in column A
rw = Application.Match("Totals", Range("A:A"),0)
With Range("D" & rw & ":F" & rw)
.Formula = "=SUM(D2:D" & rw - 1 & ")"
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).LineStyle = xlDouble
.Borders(xlEdgeBottom).Weight = xlThick
End With
End Sub
1 Guest(s)
