Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Ranges in VBA (Excel 2000 or 2003)

    Hi All,

    I am "attempting" to create a worksheet using VBA. This worksheet (Background-CG) will have variable number of rows based on user input. Within my code I am attempting to name two separate cells:
    BG_M and BG_F However, these cells will not always be in the same place depending on the user input.

    I think I have named the cells in VBA, even though I cannot "see" the names in the Name box dropdown. I can see them in Insert | Names | Define window, though.

    Range("D" & NextRow + 1).Select
    ActiveWorkbook.Names.Add _
    Name:="BG_F",_
    RefersTo:='=BAckground-CG!$D$" & NextRow + 1

    Now, in another macro, I wish to get the value of that cell named BG_F

    FemaleValue= Range("'Background-CG'!BG_F").Value

    I keep getting an error ...

    Any help would be appreciated!
    Thanks,
    --cat

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Named Ranges in VBA (Excel 2000 or 2003)

    Have you tried:
    FemaleValue= Range("BG_F").Value

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Also instead of using the ADD NAMES, it is often easier to just use the range object:
    Range("D" & NextRow + 1).Name:="BG_F"

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges in VBA (Excel 2000 or 2003)

    Hi Cindy,

    You have the quotes and single quotes wrong.

    This should do the trick:

    ActiveWorkbook.Names.Add Name:="BG_F", _
    RefersTo:="='BAckground-CG'!$D$" & nextrow + 1
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges in VBA (Excel 2000 or 2003)

    Thanks, Steve,

    Defining the name the shorter way ... works! The other way didn't work with or without the sheet name.

    Again, I'm in debt ...
    --cat

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Ranges in VBA (Excel 2000 or 2003)

    Thank you, Jan, for figuring out why the long way didn't work for me ...

    --cat

Posting Permissions

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