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

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

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

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

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

Thanks Mike

"Mike H" wrote:

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

One way:

Const csFIND As String = "*total*"
Const csFORM As String = "<your formula"
Dim rCell As Range

For Each rCell In <your dynamic range here
With rCell
If LCase(.Text) Like csFIND Then _
.Offset(0, 1).Formula = csFORM
End With
Next rCell


In article ,
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