Hi,

Apologies I sent you a version I was playing with that inserted a formula
that summed column B up to the last time total appared in column A so ignore
and use this

Sub stantial()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If InStr(UCase(c.Value), "TOTAL") 0 Then
c.Offset(, 1).Formula = "=22/7"
End If
Next
End Sub


Mike




"Mike H" wrote:

Hi,

use this

Sub stantial()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If InStr(UCase(c.Value), "TOTAL") = 0 Then
sumrows = sumrows + 1
Else
c.Offset(, 1).Formula = "=sum(B" & c.Row - sumrows & ":B" & c.Row - 1 &
")"
sumrows = 0
End If
Next
End Sub

Mike

"Enyaw" wrote:

That worked Mike. Thanks. One more thing. What if total is only part of
the cell value. How do i search for that?

"Mike H" wrote:

Hi,

This assumes you are searching column A. Right click your sheet tab, view
code and paste this in and run it. You didn't say what formula so substitute
your own.

Sub stantial()
Dim MyRange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & Lastrow)
For Each c In MyRange
If UCase(c.Value) = "TOTAL" Then
c.Offset(, 1).Formula = "=22/7"
End If
Next
End Sub

Mike

"Enyaw" wrote:

How do I search through a dynamic range in one column and for each instance
the word total appears insert a formula into the cell next to it?

Thanks in advance