Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm trying to use a formula (FINDSERIES) and I'm getting a #NAME? error. I have the following setup in VB. I've used the same function successfully in other excel files, but I'm failing here. What am I doing wrong this time?



    Public Function FindSeries(TRange As Range, MatchWith As String)

    For Each cell In TRange
    If cell.Value = MatchWith Then
    x = x & cell.Offset(0, 1).Value & ", "
    End If
    Next cell

    FindSeries = Left(x, (Len(x) - 2))

    End Function
    thanks
    christine

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is the module containing the function in the same book as where you are trying to use the function?
    If you are trying to use it in a workbook other than the one in which it is located, you must refer to the location book:
    Code:
    =Book1!FINDSERIES(a1:a10,"ASDF")
    I would also suggest using Application.Volatile in your function so that it updates when a cell is changed
    Code:
    Public Function FindSeries(TRange As Range, MatchWith As String)
    Application.Volatile
    For Each cell In TRange
    If cell.Value = MatchWith Then
    x = x & cell.Offset(0, 1).Value & ", "
    End If
    Next cell
    
    FindSeries = Left(x, (Len(x) - 2))
    
    End Function

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Did you intend to post this in Excel forum, or are you trying to use Automation of Excel in an Access VBA module? If it's the latter the automation bit would need to be defined first.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes. My apologies as this was meant to be posted in spreadsheets instead of databases. I tried the adjustments and I'm still receiving the same error. Strange!
    thanks
    christine

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post a sanitized book where you are receiving the error? You function works fine when I tried to run it.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'll move the thread to Spreadsheets then. Thanks for the reply.
    Wendell

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    The function must be in a normal module (not worksheet, workbook or class module) and you have to have macros enabled.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That was it! I had it in the workbook, not in a normal module. I put it in the wrong place. Thanks everyone!
    thanks
    christine

Posting Permissions

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