UDF code to find specific text in cell comments, then average cell values

[bruch04]

Okay, people. Can someone see an error in this UDF code??? I'm coming
up with a "#VALUE!" error.

Public Function findfuel(rng As Range)

Dim rCell As Range
Dim mytotal As Double
Dim numberofcells As Integer
Dim myaverage As Double
Const sStr As String = "fuel"

For Each rCell In rng
If InStr(1, rCell.Comment.Text, sStr, vbTextCompare) Then
mytotal = mytotal + rCell.Value
numberofcells = numberofcells + 1
myaverage = mytotal / numberofcells
End If
Next rCell
findfuel = average
End Function

Seems pretty straightforward.
Any help would be appreciated. Thanks.

 

[Rowan Drummond]

The last line of your UDF sets findfule = average not myaverage but I
think the main problem is that this will error out if any cell in you
range does not have a comment. Try it like this:

Public Function findfuel(rng As Range)

Dim rCell As Range
Dim mytotal As Double
Dim numberofcells As Integer
Dim myaverage As Double
Const sStr As String = "fuel"
Dim theCmt As Comment

For Each rCell In rng
Set theCmt = rCell.Comment
If Not theCmt Is Nothing Then
If InStr(1, theCmt.Text, sStr, vbTextCompare) > 0 Then
mytotal = mytotal + rCell.Value
numberofcells = numberofcells + 1
End If
End If
Next rCell
findfuel = mytotal / numberofcells
End Function

Note this will not recalculate when comments are changed.

Hope this helps
Rowan

 

[bruch04]

That worked perfectly. Thanks so much.

 

[Rowan Drummond]

You're welcome.