Hello everyone!
I wonder if someone can help with the below query:
I've got a sum total in column H (based on the response to thread 'sum varying ranges' and I need to copy and paste it into the adjacent cells. I've tried to copy the cell ranges (Range(Cells(i,8)etc)and used a recorder format for the copy and paste part but I can't get it right.
Sub SumWhen2blankRowsFound()
Dim i As Long
Dim LastRow As Long, NextNew
LastRow = Range("H" & Rows.Count).End(xlUp).Row
NextNew = 1
For i = 1 To LastRow + 1
If Cells(i, 8) = "" Then
Cells(i, 8).Formula = "=SUM(" & Range("H" & NextNew & ":H" & i - 1).Address & ")"
Range(Cells(i, 8).Address).BorderAround xlContinuous, xlThin
'No of empty rows
i = i + 5
NextNew = i + 1
End If
Next
End Sub
I need a sum total in the following columns : J:N, P:Q, S:T. It would also be great to include some formatting in this, colour is 35 and border is top and bottom only. Border around has been used so I ran the recorder again and tried to paste it in but it hasn't worked (it just formats the active cell) and can't find an alternative function to help.
format Macro
' Macro recorded 24/03/2010 by RS
'
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End Sub
Again, huge thanks and much respect in anticipation
Bookmarks