How to search the characters of a cell for a list of values

Question

How to search the characters of a cell for a list of values

Navigation

#1 by (0 votes)

0

I have several cells with random characters

123456- foo # 1 -rfjas1
123456rfja- foo # 1 -s1
1- foo # 2 -23456rfjas1
123- foo # 3 -456rfjas1
123456rf- foo # 3 -jas1

I should look for the values foo # N these I have stored in a range

foo # 1
foo # 2
foo # 3
foo # 4

I need to look up all the values in the foo # N list within each cell in the list above.

perform a function to return this value

Function BuscarFixed(texto As Range, listado As Range) As String

Dim i As Long
For i = 1 To listado.Rows.Count
If IsError(Application.WorksheetFunction.Find(listado.Cells(i, 1).Value, texto)) Then
Else
BuscarFixed = Application.WorksheetFunction.Find(listado.Cells(i, 1).Value, texto)
Exit For
End If

Next i



End Function

But it does not work, how can I search a cell for each value in a list and return the found value if it exists? I would greatly appreciate your help.

vba excel

asked by onerom 01.06.2018 в 18:45

source

1 answer

Error Failed to execute aapt, when compiling project in React-Native Receive foreach from Laravel

score 0 · Accepted Answer

I found it! the problem is that Application.WorksheetFunction.Find() returns an error when it does not find the value change it by the function InStr() that returns 0 if it does not find the value, with this code you can search a range within a cell and return the found value.

Function BuscarFixed(comentario As Range, matriz As Range) As Variant

Dim resultado As String
Dim i As Long
Dim found As String
For i = 1 To matriz.Rows.Count
resultado = ""
found = InStr(comentario.Value, matriz.Cells(i, 1).Value)
If found < 1 Then
resultado = "no encontrado"
Else
resultado = matriz.Cells(i, 1).Value
Exit For
End If
Next i
BuscarFixed = resultado
End Function