Refer to named cell or range using VBA?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Refer to named cell or range using VBA?

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Refer to named cell or range using VBA?

     
    If I name a cell or range of cells in a worksheet using the Insert | Name | Define, can I use/refer to that name using VBA? The reason I ask is that I have a loop of of goal seeks that I recorded and copied that simply refer to cell addresses. The problem is that when I add/remove rows/columns, the goal seek gets messed up. So I thought that if I there was a way to refer to names of cells rather than cell addresses, this could be resolved. Thanks much in advance. Belwo is a sample of the code that I am trying to use (It's recorded and edited slightly b/c I'm such a noob at VBA.)

    Code:
    Application.ScreenUpdating = False
    
    'Revenue
        Range("Q9").Select
        Range("Q9").GoalSeek Goal:=Range("AE9"), ChangingCell:=Range("Z9")
        Range("S9").Select
        Range("S9").GoalSeek Goal:=Range("AF9"), ChangingCell:=Range("AA9")
        Range("U9").Select
        Range("U9").GoalSeek Goal:=Range("AG9"), ChangingCell:=Range("AB9")
        Range("W9").Select
        Range("W9").GoalSeek Goal:=Range("AH9"), ChangingCell:=Range("AC9")
        
    'Cost of Sales
        Range("Q10").Select
        Range("Q10").GoalSeek Goal:=Range("AE10"), ChangingCell:=Range("Z10")
        Range("S10").Select
        Range("S10").GoalSeek Goal:=Range("AF10"), ChangingCell:=Range("AA10")
        Range("U10").Select
        Range("U10").GoalSeek Goal:=Range("AG10"), ChangingCell:=Range("AB10")
        Range("W10").Select
        Range("W10").GoalSeek Goal:=Range("AH10"), ChangingCell:=Range("AC10")
      
    'Selling, General & Adminsitrative
        Range("Q13").Select
        Range("Q13").GoalSeek Goal:=Range("AE13"), ChangingCell:=Range("Z13")
        Range("S13").Select
        Range("S13").GoalSeek Goal:=Range("AF13"), ChangingCell:=Range("AA13")
        Range("U13").Select
        Range("U13").GoalSeek Goal:=Range("AG13"), ChangingCell:=Range("AB13")
        Range("W13").Select
        Range("W13").GoalSeek Goal:=Range("AH13"), ChangingCell:=Range("AC13")
    Luke Thomas

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,757
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes..

    if named range is say "myrange"
    use

    Range("myrange")
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,757
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default

    However, you still have the same problem with named ranges...
    when you insert/delete rows - the named range adjusts accordingly...

    you can get around this by using the inderect function in the named range definition...

    instead of named range referring to
    Sheet1!$A$1:$A$100

    use
    INDIRECT("Sheet1!$A$1:$A$100")
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks gentlemen.
    Luke Thomas

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    135
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Will this also work in the case of naming worksheet tabs based on cell references on other worksheets? Right now I have it set up so that worksheet tabs are named based on a cell value in the respective worksheet with this code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NameRange As Range
    Set NameRange = Range("B4")
    If Not Intersect(Target, NameRange) Is Nothing Then
        ActiveSheet.Name = CStr(NameRange)
    End If
    End Sub

    But when I change Range ("B4") to Range("CompanyAName"), which is defined as =INDIRECT("ReqInfo!$F$7") as suggested earlier, it doesn't work and I get a debug error on this line. Thoughts?
    Luke Thomas

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,757
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I would think I worked the first time you ran it, but subsequently not...

    because the Indirect in the defined name HARD CODED the Original Sheet name in the range...

    so the first time it ran, it renamed it to whatever was in that cell...
    but the defined name stayed referenced to the original sheet name...
    so subsequent runing the macro, it is trying to find the ORIGINAL sheet name...and of course not finding it.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com