Formula depending on cell value

[Enyaw]

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

 

[Mike H]

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

 

[JE McGimpsey]

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

 

[Enyaw]

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]

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

 

[Mike H]

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

 

[Enyaw]

Thanks Mike

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]

your welcome

Thanks Mike