Find "x", count up and store # of rows

[ker9]

How would I write something that would

Do Loop

start at bottom (xlend) and work up

Find "GroupChange" in column N (0,14)

Count rows UP to next "GroupChange and store number
(can store it in column O on same line as found GroupChange)

Maybe insert a blank row after GroupChange to insert formula that uses that number of rows (sum up x rows)

until it can't find it

Thanks!

[ker9]

This code works, but inserts hard coded number, where I'd like formula

It sums up to next blank cell (active cell is blank and receives sum total)

ActiveCell.Value = WorksheetFunction.Sum(Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)))

This doesn't work, anyone know how to make it work?

ActiveCell.FormulaR1C1 = "=Sum(Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp)))"

Thanks!

[ker9]

I figured it out - for anyone interested:

Dim rFind As Range, sAddr As String
 
With Sheets("Data").UsedRange.Columns(15)
    Set rFind = .Find(What:="GroupChange", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        sAddr = rFind.Address
        Do
                rFind.Offset(-1, -9).Formula = "=SUM(" & rFind.Offset(-2, -9).Address(0, 0) & ":" & rFind.Offset(-2, -9).End(xlUp).Address(0, 0) & ")"
             Set rFind = .FindNext(rFind)
         Loop While rFind.Address <> sAddr
    End If
End With
End Sub

[StephenR]

Thank you for posting your solution. That code looks familiar...