Function SumFormulas(R As Range) As Double
Dim cel As Range
For Each cel In R
SumFormulas = SumFormulas + cel.Value
Next
End Function
Sub InsertBlankRow()
Dim lngRow As Long
With Sheets("Budget")
For lngRow = 9 To .UsedRange.Rows.Count
If InStr(UCase(.Cells(lngRow, "B")), "TOTAL") > 0 Then
.Rows(lngRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next
End With
End Sub
Sub TEST()
' By ProfJimJam
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
StartRow = 1
For i = StartRow To LastRow
If InStr(UCase(Trim(Cells(i, "B"))), "TOTAL") And i > StartRow Then
Cells(i, "U").Formula = "=SUM(S" & StartRow & ":S" & i - 1 & ")"
StartRow = i + 1
End If
Next
End Sub
EE.xlsm
Sub Test()
'by ProfJimJam
For lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 1 Step -1
If InStr(UCase(Trim(Cells(lRow, "B"))), "TOTAL") Then Rows(lRow).EntireRow.Insert
Next lRow
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
StartRow = 1
For i = StartRow To LastRow
If InStr(UCase(Trim(Cells(i, "B"))), "TOTAL") And i > StartRow Then
Cells(i, "C").Formula = "=SUM(C" & StartRow & ":C" & i - 1 & ")"
Cells(i, "D").Formula = "=SUM(D" & StartRow & ":D" & i - 1 & ")"
Cells(i, "E").Formula = "=SUM(E" & StartRow & ":E" & i - 1 & ")"
Cells(i, "F").Formula = "=SUM(F" & StartRow & ":F" & i - 1 & ")"
Cells(i, "G").Formula = "=SUM(G" & StartRow & ":G" & i - 1 & ")"
Cells(i, "H").Formula = "=SUM(H" & StartRow & ":H" & i - 1 & ")"
Cells(i, "I").Formula = "=SUM(I" & StartRow & ":I" & i - 1 & ")"
Cells(i, "J").Formula = "=SUM(J" & StartRow & ":J" & i - 1 & ")"
Cells(i, "K").Formula = "=SUM(K" & StartRow & ":K" & i - 1 & ")"
Cells(i, "L").Formula = "=SUM(L" & StartRow & ":L" & i - 1 & ")"
Cells(i, "M").Formula = "=SUM(M" & StartRow & ":M" & i - 1 & ")"
Cells(i, "N").Formula = "=SUM(N" & StartRow & ":N" & i - 1 & ")"
StartRow = i + 1
End If
Next
End Sub
EE.xlsm
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
conditional formatting | 4 | 43 | |
Select/Copy row and pasting it lower in sheet | 7 | 21 | |
VBA Fill Blanks with text from another cell | 6 | 23 | |
How to get populate a column with a string using excel formula | 5 | 9 |
Join the community of 500,000 technology professionals and ask your questions.