+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    bruch04
    Guest

    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.


  2. #2
    Rowan Drummond
    Guest

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


  3. #3
    bruch04
    Guest

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

    That worked perfectly. Thanks so much.


  4. #4
    Rowan Drummond
    Guest

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

    You're welcome.

    bruch04 wrote:
    > That worked perfectly. Thanks so much.
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1