how o recognize a specific letter in different places in a cell??

  • Thread starter Thread starter Ksenija
  • Start date Start date
K

Ksenija

Hi,

I have a lots of cells in which there it written things like these:

E2245_Gv070830f 3,5_3,5_01
E224_Gv070830o 3,5_3,5_01
E224_Gv071220f 4_4_01

I want to select all cells in which letter "o" or "f" is present. How can i
write this in code?? these letters can have different places in the cell..
 
Try this:
With Worksheets(1).Range("a1:a500")
Range("a1").Activate
Set c = .Find("o", LookIn:=xlValues)
SelAddr = ""
If Not c Is Nothing Then
firstAddress = c.Address
SelAddr = c.Address
Do
Set c = .FindNext(c)
SelAddr = SelAddr & "," & c.Address
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Range("a1").Activate
Set c = .Find("f", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
If SelAddr <> "" Then SelAddr = SelAddr & ","
SelAddr = SelAddr & c.Address
Do
Set c = .FindNext(c)
SelAddr = SelAddr & "," & c.Address
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Range(SelAddr).Select
End With

It looks for any cells containing the letters "o" or "f" in any position. It adds the addresses of any that it finds to a string (SelAddr) then finally selects it.
HTH
Paul
 
Back
Top