+ Reply to Thread
Results 1 to 10 of 10

SUM function with variable end range -- "#NAME?" error

  1. 06-12-2013, 11:38 AM #1
    NicholasL
    NicholasL is offline
    Registered User
    Join Date
    06-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    64

    SUM function with variable end range -- "#NAME?" error

    Hello, so I'm working on this bit of code and the whole thing gave me an error so I started blocking it off line by line to find out what exactly is going on. The last line that isn't a comment ( with the SUM function -- "activecell.value = "=(SUM(B2: endrow) -1 ) / (10-1)" )

    So that line is giving me a "#NAME?" error in the cell I'm putting that formula in. I imagine it has something to do with my syntax with the variable. Any suggestions? Thanks!

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 06-12-2013, 11:41 AM #2
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: SUM function with variable end range -- "#NAME?" error

    As an fyi, you don't need to comment out your code when you can step through each line individually by pressing F8. It's a huge time saving tip in my opinion and I was very happy to learn that early on in my coding career.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---
    Reply With QuoteRegister To Reply
  3. 06-12-2013, 11:45 AM #3
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: SUM function with variable end range -- "#NAME?" error

    try 
    Please Login or Register  to view this content.
    You can't use endrow in quotations because the VBE sees everything within quotes as a string type so you have to separate the string and insert the range between the quotes. I don't know if this will solve your problem but it's a start.
    Reply With QuoteRegister To Reply
  4. 06-12-2013, 11:46 AM #4
    NicholasL
    NicholasL is offline
    Registered User
    Join Date
    06-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    64

    Re: SUM function with variable end range -- "#NAME?" error

    Thanks for the tip!
    Reply With QuoteRegister To Reply
  5. 06-12-2013, 11:52 AM #5
    NicholasL
    NicholasL is offline
    Registered User
    Join Date
    06-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    64

    Re: SUM function with variable end range -- "#NAME?" error

    Hmm...now it's just giving me a 400 error.
    Reply With QuoteRegister To Reply
  6. 06-12-2013, 11:54 AM #6
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: SUM function with variable end range -- "#NAME?" error

    maybe 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-12-2013, 12:02 PM #7
    BigBas
    BigBas is offline
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127

    Re: SUM function with variable end range -- "#NAME?" error

    Nicholas,

    The problem is in fact in your syntax. In the following line: 
    Please Login or Register  to view this content.
    All of the data between the "quotation marks" will be treated as text, including the word "endrow". Try changing it to something like:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-12-2013, 12:06 PM #8
    Mordred
    Mordred is offline
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: SUM function with variable end range -- "#NAME?" error

    Hi BigBas the line 
    Please Login or Register  to view this content.
    has an extra " and should be 
    Please Login or Register  to view this content.
    Last edited by Mordred; 06-12-2013 at 12:11 PM.
    Reply With QuoteRegister To Reply
  9. 06-12-2013, 12:16 PM #9
    NicholasL
    NicholasL is offline
    Registered User
    Join Date
    06-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    64

    Re: SUM function with variable end range -- "#NAME?" error

    Replied before refreshing -- I'm trying the new solution.
    Last edited by NicholasL; 06-12-2013 at 12:21 PM.
    Reply With QuoteRegister To Reply
  10. 06-12-2013, 12:25 PM #10
    NicholasL
    NicholasL is offline
    Registered User
    Join Date
    06-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2011
    Posts
    64

    Re: SUM function with variable end range -- "#NAME?" error

    The code you suggested worked! Thank you very much, both of you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1