Results 1 to 6 of 6

Refer to named cell or range using VBA?

This is a discussion on Refer to named cell or range using VBA? within the Excel Questions forums, part of the Question Forums category; If I name a cell or range of cells in a worksheet using the Insert | Name | Define, can ...

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Posts
    135

    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
    31,944

    Default

    Yes..

    if named range is say "myrange"
    use

    Range("myrange")
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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
    31,944

    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")
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

    Default

    Thanks gentlemen.
    Luke Thomas

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Posts
    135

    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
    31,944

    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.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    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

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