Creating a function with a Boolean variable

[Kentucky_User]

I'd appreciate any insight from someone experience with writing VBA functions.

I am trying to write a function that returns a Boolean value. The function has two arguments, a Range argument and string argument. The function is supposed to take the value of a string and search a range for the value. If the function finds the string value, the function is true. If not, it is false.

The function is embedded in a worksheet with a cell that returns the value of the Boolean variable. The function is written in the cell as such: FunctionName(E:4, B1:B13).

The problem I am having is that nothing is returned into the cell where the function is written. The variable "name" is linked to cell E:4 and the variable "address" is linked to range B1:B13. Here is my VBA code:

Function NameExists(name As String, address As Range) As Boolean


Dim cell As Range
NameExists = False


With Worksheets("Sheet1").Range("address")
If Range("address").Text = Range("name").Text Then
NameExists = True
Else
NameExists = False
End If
End With


End Function

 

[sericom]

Try this

Function NameExists(name As String, address As Range) As Boolean
NameExists = False
For Each c In address
If c.Value = name Then
NameExists = True
End If
Next c
End Function

And formula would be
NameExists(E:4, B1:B13)

 

[JoeMo]

This will look for the string you enter in the range you select. It's not case sensitive, but you can change that by changing the "false" argument to "true".

Function NameExists(Nam As String, R As Range) As Boolean
Dim Test As Range
Set Test = R.Find(Nam, R.Cells(1, 1), xlFormulas, xlPart, , xlNext, False)
If Not Test Is Nothing Then NameExists = True
End Function

 

[krausr79]

A couple notes on your original code:

Putting reserved words as variables will make your code screwy. 'Cells' is already a thing. 'Address' seems like a thing but I don't think it actually is. Stuff like 'TheCells' or 'AddressInput' is safer.

Once things are variables, they don't want quotes anymore when being used:
Dim S as string
S = "Test"
msgbox("S") 'result will be S
msgbox(S) ' result will be Test

 

[Kentucky_User]

Thanks! This worked.

Try this

Function NameExists(name As String, address As Range) As Boolean
NameExists = False
For Each c In address
If c.Value = name Then
NameExists = True
End If
Next c
End Function

And formula would be
NameExists(E:4, B1:B13)

 

[Rick Rothstein]

This will look for the string you enter in the range you select. It's not case sensitive, but you can change that by changing the "false" argument to "true".

Function NameExists(Nam As String, R As Range) As Boolean
Dim Test As Range
Set Test = R.Find(Nam, R.Cells(1, 1), xlFormulas, xlPart, , xlNext, False)
If Not Test Is Nothing Then NameExists = True
End Function

If the "xlPart" in your code is correct, then the OP needs to tell us if he wants True returned if the word being searched for is embedded in a longer word. For example, should a search for the word "other" be reported as True if the only place it exists is inside the word "brothers".

 

[JoeMo]

If the "xlPart" in your code is correct, then the OP needs to tell us if he wants True returned if the word being searched for is embedded in a longer word. For example, should a search for the word "other" be reported as True if the only place it exists is inside the word "brothers".

Of course, but it looks like the OP has found a satisfactory solution so whether its xlPart or xlWhole is a "don't care".