I've been browsing these forums for a while -- it is such a wonderful resource. I was able to find some VBA codes that will allow me to add an extra row whenever the word "Transfer" appears and to sum the values below the blank row that was added. I need to adjust this range to include one extra cell after the blank row but can't seem to figure it out. Is it possible to look for the blank row + 1? Also, is it possible to combine this action into one macro, rather than having to run multiple macros to get the result I'm looking for? I've attached a copy of the test document I'm using.
Thanks! I appreciate your insight!
Sub add_row() Dim x& x = Cells(Rows.Count, "B").End(xlUp).Row While x <> 0 If UCase(Cells(x, "B").Value) = UCase("transfer") Then Rows(x).Insert End If x = x - 1 Wend End Sub Sub sum_cells() Dim r As Range For Each r In Columns("F").SpecialCells(2, 1).Areas If r(0).Value = "" Then With r(0).Resize(, 1) .Formula = "=subtotal(9," & r.Address & ")" .EntireRow.Range("a1:bg1").Interior.Color = RGB(255, 255, 153) End With End If Next End Sub