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

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.

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

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 wrote:
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.

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

That worked perfectly. Thanks so much.

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

You're welcome.

bruch04 wrote:
That worked perfectly. Thanks so much.