MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Any ideas why this Function won't work?


Posted by Aron on January 04, 2002 7:46 PM

Function findF(Rng As Range, Txt As String) As Boolean
If Rng.Comment.Text Like Txt Then
Rng.Text = "True"
End If
End Function

Sorry this is so Basic. I'm new to VBA. Thanks for your help!


Posted by Russell Hauf on January 04, 2002 9:30 PM

You're close - try this:

Function findF(Rng As Range, Txt As String) As Boolean
If Rng.Comment.Text Like "*" & Txt & "*" Then
findF = True
End If
End Function

Hope this helps,

Russell

Posted by Bariloche on January 04, 2002 9:51 PM

rm,

One of the reasons why the Function procedure isn't working is because its not RETURNING anything. You haven't assigned the value of the function to the function's name. If you look at Russell's code you'll see he made that correction.

The other thing that is a no-no is you were trying to get the function to manipulate a range (Rng.Text = "TRUE"). Functions can't modify cells.

You didn't say exactly what the nature of your error was, but when I was playing around with your function I'd get an error if the cell didn't have a comment. Here's my code/function snippet for you to study:

Sub UseFunction()

Dim Rng As Range
Dim Txt As String

Set Rng = ActiveCell
Txt = Cells(2, 1).Value
Rng.Value = findF(Rng, Txt)

End Sub
Function findF(Rng As Range, Txt As String) As Boolean

If Rng.Comment Is Nothing Then Exit Function
If (Rng.Comment.Text = Txt) Then findF = "True"

End Function


have fun

Posted by Aron on January 05, 2002 6:12 AM

Thank you! My first Function successful!