+ Reply to Thread
Results 1 to 6 of 6

Determining if the value of a cell can be a named range, then assigning named ranges after

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Determining if the value of a cell can be a named range, then assigning named ranges after

    Hey Everyone,

    I have a function that assigns the each range a name based on the header.

    Please Login or Register  to view this content.
    That works.

    However, I would like to inform users when the headers aren't valid for named range, and then explain to them why. I know that a runtime error does the same, but people get freaked out by errors.

    I want to let the user know if a) the contents of the header have a space in them, b) the header starts with a number c) there is an empty header d) it includes a comma. Thus far I am trying the following but getting caught up in several places.

    Please Login or Register  to view this content.
    If I could get some help with this I would be immensely grateful.
    Last edited by Romulo; 09-15-2013 at 02:20 PM.

  2. #2
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Determining if the value of a cell can be a named range, then assigning named ranges a

    Perhaps this will get you started

    Please Login or Register  to view this content.
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown

  3. #3
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: Determining if the value of a cell can be a named range, then assigning named ranges a

    Thanks, I need to use the CurrentRegion though because it has to be for any given block of data.

    My bigger question is if this the right approach a serious of If statements to check if the contents of the cell have something inside that makes them invalid for a named range. Is there a simple, more elegant solution? If this is the right approach, do I need to use the InStr, or do I need to use another string related command?

  4. #4
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Determining if the value of a cell can be a named range, then assigning named ranges a

    Thanks, I need to use the CurrentRegion though because it has to be for any given block of data.
    'selecting the top row only
    Which is correct? The code I posted covers from the first used cell in the top row to the last used cell in the top row (i.e. quote 2). If you are going from the activecell then use...
    Please Login or Register  to view this content.
    You dont use "areatotal" anywhere else other than when using it for the header row so why do you need currrentregion?

    do I need to use the InStr, or do I need to use another string related command?
    Are you saying the code posted didn't produce the message boxes
    Last edited by WasWodge; 09-15-2013 at 05:46 PM.

  5. #5
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: Determining if the value of a cell can be a named range, then assigning named ranges a

    Well I wanted to select the top rows of the CurrentRegion. I realized i was messing up the ranges. Thank you for the idea, to just get the corner region and use End.Xl function. That should have been obvious but I am relative newbie. Thanks though!

    The message boxes were produced, and then the first loop I posted does work. Once I added additional Error Handling (On error GoTo) then it tells you what headers are improper, and then continue the loop without interruption, taking the correct headers down. Solved!

  6. #6
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Determining if the value of a cell can be a named range, then assigning named ranges a

    If it is solved
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

    which will keep the moderators happy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combine all Named Ranges into one Named Range
    By thinkspac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-23-2020, 07:52 PM
  2. I have 2 named ranges on 1 sheet and 1 named range on another..
    By niceguy21 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2013, 06:08 AM
  3. Named ranges-Should I use a dynamic named range
    By foseco in forum Excel General
    Replies: 4
    Last Post: 06-11-2009, 03:56 PM
  4. Assigning dynamic named ranges to listfillrange via macro code
    By Schwizer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2007, 11:44 AM
  5. Replies: 1
    Last Post: 03-21-2006, 06:40 PM

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