Excel VBA Question

Do you mean a Function or a Sub Routine - sounds more like a Function to me - ie along the lines of:

Function NameExists(rngNames As Range, strName As String) As Boolean
NameExists = Not rngNames.Find(What:=strName, LookAt:=xlWhole) Is Nothing
End Function

Hi

You could just use the WorksheetFunction Countif to do this:


Dim blnExists As Boolean

blnExists = Application.WorksheetFunction.Countif(Range("A1:G100"),"Bob")

'blnExists True if present, False otherwise

Note that Countif isn't case-sensitive - if you want case-sensitivity you could use the Find function instead:


Dim rng As Range

Set rng = Range("A1:G100").Find(What:="Bob",LookIn:=xlValues,MatchCase:=True,LookAt:=xlWhole)

'test to see if found:

If Not rng Is Nothing Then
  'value found!
Else
  'Value not found!
End If


Make sense?

Richard

No it does not make sense. I am so sorry,  I don't know VBA to well.  Can you please write it in a sub format.

I tried putting your code in a sub format but it did not work:

Sub NameExist()
Dim blnExists As Boolean

blnExists = Application.WorksheetFunction.CountIf(Range("A1:G100"), "Bob")

'blnExists True if present, False otherwise

End Sub

Please let me know what I did wrong.

If you want to use a separate call to return a value you should use a Function as outlined.

Paste the code I provided into a standard Module, you can then call the Function from your other code, eg:

Sub Example()
Dim strName As String
strName = "Bob"
If NameExists(Range("A1:G100"), strName) Then MsgBox strName & " Has Been Found", vbInformation, "Hooray"
End Sub

Note: per Richard's code if you want case sensitivity (ie Bob <> bob) then you must add the MatchCase argument to the Find call.

To see the result using the COuntif you could amend the Sub code you had to:

Sub NameExist()
Dim blnExists As Boolean
Dim strValue As String

strValue = "Bob"

blnExists = Application.WorksheetFunction.CountIf(Range("A1:G100"), strValue)

If blnExists Then
  MsgBox strValue & " Has Been Found", vbInformation, "Hooray"
Else
  MsgBox strValue  & " Has Not Been Found", vbInformation, "YaBoo Sucks!"

End Sub


Richard

Richard, would you really advocate a Sub Routine given the request was to "Return a Boolean" ?

I appreciate that a Sub was explicitly requested but I suspect that's more down to familiarity than anything else.  A Sub is illogical here IMO.

I am sorry I am confuse.  Can you please send me and example in and blank excel sheet.  I thing once I see the way it should be setup, I will be able to grasp it.
Tester-a-v1.0.xls

Guys Thank you so much!! I was so confused at first, but now I understand.

DonkeyOte I didn't see your question at first. I meant a Sub-Routine.  

I'm glad you have resolved ... as a general principle though if you're "returning" a value you should use a Function rather than a Sub - a Sub won't pass a result back directly whereas a Function will.

Though by no means meant as definitive synopsis it's often useful to distinguish between the two along the lines of:

A Sub is used to perform an action (or actions)

A Function is used to return a value.

Chip Pearson's synopsis is, as you would expect, much more eloquent and informative:

http://www.cpearson.com/Excel/differen.htm

Wow!! very informative!  Thanks you!!

Luke

Based on the original post, I would have thought a Function to be the appropriate solution :)

Just glad that MasterOfTheSky is happy with the help!

Richard

Mr S - you've scuppered my anonymity...

Lol!! I didn't realize you guys know one another.  

Yep - all those kids'll be chasing after you trying ot feed you carrots...

Yes we do - as a general rule nerds know other nerds... quite a few of us know one another
(though I'm very much a noob round these here parts)

lol!! okay.