Easy VBA need expert help: search

[mrchonginhk]

I need to write a FUNCTION called

Finding(SearchValue As Variant,Rng As Range) As Boolean

which check if a particular value is located within that range.

Pls help.

 

[Tazguy37]

Why not use the Find Method instead? It's already there for you to use. Are there restrictions on any of your inputs or anything like that?

 

[mrchonginhk]

Yes I know. But it is a VBA exercise after the class. Pls help.

 

[Nimrod]

Public Function Finding(SearchValue As Variant, Rng As String) As Boolean
With Range(Rng)
Set c = .Find(SearchValue, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
Finding = True
Else
Finding = False
End If
End With
End Function


OR.......

Public Function Finding(SearchValue As Variant, Rng As String) As Boolean
If Application.WorksheetFunction.CountIf(Range(Rng), SearchValue) Then
Finding = True
Else
Finding = False
End If
End Function

 

[Nimrod]

Here's version 3 ... :wink:

Public Function Finding(SearchValue As Variant, Rng As String) As Boolean
Finding = Application.WorksheetFunction.CountIf(Range(Rng), SearchValue)
End Function

 

[Nimrod]

Version 4 ...

Public Function Finding(SearchValue As Variant, Rng As Range) As Boolean
Finding = Application.WorksheetFunction.CountIf(Rng, SearchValue)
End Function

 

[Nimrod]

Test/demo script for Ver1,2,3 is ...

Public Sub testFinding()
MsgBox Finding("Hello", "A1:A20")
End Sub

Test/demo script for Ver 4 is ...

Public Sub testFinding()
MsgBox Finding("Hello", Range("A1:A20"))
End Sub

 

[mrchonginhk]

The 1st version returns #Value
What could be wrong ?

I am trying the others.

My hat off!!

Thanks.

 

[Nimrod]

Are you looking for a UDF ( ie. a user defined function that called from a cell ) or are you looking for a VBA function that is called from another VBA procedure ??

 

[mrchonginhk]

The other version works !!! Thanks.

Is it I must put double quote like

=FINDING("ABC",Range) ??
Thanks.