Looping through text

[Fish]

Morning group,

I had a question in reference to looping through a text
string to find text within the string.

Basically what I'm trying to do is have a macro go into a
cell and see if the specified text exists in that string.
The string is located 31 columns to the left of the
starting position of data entry but when i go to look up
the value i get an error. Thanks in advance. Code is down
below

Regards,

Jeff


Sub Textloop()

Dim IGR As Integer
Dim BUnt As String
Dim CellSr As Range, Stgsr As String
BUnt = "shoes"
Stgsr = "XYZ"
ActiveCell.Select

''''This doesnt work as a value i need it to read 31 cells
'''to the left of where i am this doesn't work
CellSr = Range(0, -31).Value

'''Finds the text Search string
For IGR = 1 To Len(CellSr)
Debug.Print Mid(UCase(CellSr), IGR, Len(Stgsr))
If Mid(UCase(CellSr), IGR, Len(Stgsr)) = UCase(Stgsr)
Then
'''''''''Trigger now sets to another coumn
ActiveCell.Value = BUnt
End If
Next IGR


End Sub

 

[Tom Ogilvy]

No need to loop. Use Instr

if Instr(1,Cell.Value, "abcd",vbTextcompare) > 0 then
msgbox "abcd is in " & cell.Value
End if