Macro 2 insert formula if part of cell contains specific text

[Joek]

I am trying to create macro that will insert a formula in a cell depending on
part of the contents of another cell.

E.G.
If cell C2 contains the text " v " (lower case v with spaces each side) then
input a formula in C4

I need a macro because I nned to check a list of entries for three different
part text entries.

I can do the loop and IF etc its just the 'If the cell contains.......

 

[Bob Phillips]

What range are you checking, normally the code goes down a column, but you
are updating later in the column.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Per Jessen]

Hi

Her's a way to do it. The line "Found= Application...." will throw an error
if " v " isn't found, so the error handler will jump to ResumeNext.

Sub test()
On Error GoTo ResumeNext
For Each cell In Selection
Found = Application.WorksheetFunction.Find(" v ", cell)
cell.Offset(0, 1).Value = cell.Row
ResumeNext:
Next
On Error GoTo 0 ' Reset error handler
End Sub

Regards,
Per

 

[Joek]

Here is the macro:

Sub Macro2()

Dim myv As String
Dim mys As String

myv = " v "
mys = " - "

Range("E2").Select

Do Until Selection.Offset(0, -2) = ""
If Selection.Offset(0, -2).xlContains = myv Then
Selection.FormulaR1C1 = "=LEFT(RC[-2],FIND("" v "",RC[-2]))"
ElseIf Selection.Offset(0, 2).xlContains = mys Then
Selection.FormulaR1C1 = "=LEFT(RC[-2],FIND("" - "",RC[-2]))"
Else
Selection.Formula = ""
End If

Selection.Offset(1, 0).Select

Loop

End Sub

 

[Don Guillett]

If you are looking for more than one instance have a look in the vba help
index for FINDNEXT.

 

[Bob Phillips]

Sub Macro2()

Dim myv As String
Dim mys As String

myv = " v "
mys = " - "

Range("E2").Select

Do Until Selection.Offset(0, -2) = ""
With Selection
If .Offset(0, -2).Value Like "*" & myv & "*" Then
.FormulaR1C1 = "=LEFT(RC[-2],FIND("" v "",RC[-2]))"

ElseIf .Offset(0, 2).Value Like "*" & mys & "*" Then
Selection.FormulaR1C1 = "=LEFT(RC[-2],FIND("" -
"",RC[-2]))"
Else
.Formula = ""
End If

.Offset(1, 0).Select
End With
Loop

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Here is the macro:

Sub Macro2()

Dim myv As String
Dim mys As String

myv = " v "
mys = " - "

Range("E2").Select

Do Until Selection.Offset(0, -2) = ""
If Selection.Offset(0, -2).xlContains = myv Then
Selection.FormulaR1C1 = "=LEFT(RC[-2],FIND("" v
"",RC[-2]))"
ElseIf Selection.Offset(0, 2).xlContains = mys Then
Selection.FormulaR1C1 = "=LEFT(RC[-2],FIND("" -
"",RC[-2]))"
Else
Selection.Formula = ""
End If

Selection.Offset(1, 0).Select

Loop

End Sub

What range are you checking, normally the code goes down a column, but
you
are updating later in the column.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)