Isnumber Search Formula

[hrayani]

Hello All,

Here is the sample DATA in range (B2:B10)

[TABLE="width: 169"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Yamaha[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[/TR]
[TR]
[TD]Suzuki[/TD]
[/TR]
[TR]
[TD]Ducati[/TD]
[/TR]
[TR]
[TD]Ducati[/TD]
[/TR]
</tbody>[/TABLE]

I want formula in Range (A2:A10)

I am using this formula in cell A2 to return 1 if the condition is met

[B]=IF(ISNUMBER(SEARCH($B$1,B2)),1,0)[/B]

All I want is to search if multiple entries are there in cell B1

For example To, Duca


Thanks in Advance

Regards,

Humayun

 

[Fluff]

Ok, how about

Function hrayani(Cl As Range, Crit As Range) As Long
   Dim Sp As Variant
   Dim i As Long
   Sp = Split(Crit, ", ")
   hrayani = 0
   For i = 0 To UBound(Sp)
      If InStr(1, Cl, Sp(i), vbTextCompare) > 0 Then
         hrayani = 1
         Exit Function
      End If
   Next i
End Function

 

[hrayani]

Hi Fluff,

Working 100% OK. Exactly what I wanted....... PERFECT

Bunch of Thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[hrayani]

Hi Fluff,

You provided me this code few days back & that time it worked perfectly fine.

Function hrayani(Cl As Range, Crit As Range) As Long   Dim Sp As Variant
   Dim i As Long
   Sp = Split(Crit, ", ")
   hrayani = 0
   For i = 0 To UBound(Sp)
      If InStr(1, Cl, Sp(i), vbTextCompare) > 0 Then
         hrayani = 1
         Exit Function
      End If
   Next i
End Function

But now when I am applying this code to my data than its not working

When I use it for a single cell i.e. =hrayani(B2,$B$1) - it works fine

But when I apply a range i.e. =hrayani(B2:B10,$B$1) - its giving error value

Can you please help me out... what I am doing wrong. Coz I remeber that day it was working fine with the range of cells

Regards,

Humayun

 

[Fluff]

It's not designed to work on a range of cells, just 1.
You need to put the formula on each row in the sheet.

 

[hrayani]

My bad....

there you go.... I forgot to drag the formula down...
Bunch of thanks

 

[Fluff]

You're welcome.