• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

Excel VBA Question

I am trying to write a NameExists function subroutine of type Boolean that takes two arguments of type Range and String, respectively.  It will search all of the cells in the given range and check wither any of them have their value property equal to the given string.  For example, if the string is "Bob", it check whether "Bob" is in any of the cells.  A cell containing "John Bob" wouldn't count. The contents have to be "Bob" exactly.  if so, it returns True. Otherwise , it returns False.
0
MasterOfTheSky
Asked:
MasterOfTheSky
  • 8
  • 6
  • 5
2 Solutions
 
DonkeyOteCommented:
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
0
 
RichardSchollarCommented:
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
0
 
MasterOfTheSkyAuthor Commented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DonkeyOteCommented:
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.
0
 
RichardSchollarCommented:
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
0
 
RichardSchollarCommented:
I missed an End if off the code, that should have been:

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 If

End Sub
0
 
DonkeyOteCommented:
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.
0
 
MasterOfTheSkyAuthor Commented:
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
0
 
DonkeyOteCommented:
Here is one version - using Function based approach.
Q-25848946-MasterOfTheSky.xls
0
 
MasterOfTheSkyAuthor Commented:
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.  
0
 
DonkeyOteCommented:
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.
0
 
DonkeyOteCommented:
Chip Pearson's synopsis is, as you would expect, much more eloquent and informative:

http://www.cpearson.com/Excel/differen.htm
0
 
MasterOfTheSkyAuthor Commented:
Wow!! very informative!  Thanks you!!
0
 
RichardSchollarCommented:
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
0
 
DonkeyOteCommented:
Mr S - you've scuppered my anonymity...
0
 
MasterOfTheSkyAuthor Commented:
Lol!! I didn't realize you guys know one another.  
0
 
RichardSchollarCommented:
Yep - all those kids'll be chasing after you trying ot feed you carrots...
0
 
DonkeyOteCommented:
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)
0
 
MasterOfTheSkyAuthor Commented:
lol!! okay.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 8
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now