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.

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.

That worked perfectly. Thanks so much.

You're welcome.

bruch04 wrote:
That worked perfectly. Thanks so much.