Refer to named cell or range using VBA?

[McGu]

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.)

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")

 

[Jonmo1]

Yes..

if named range is say "myrange"
use

Range("myrange")

 

[Jonmo1]

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")

 

[McGu]

Thanks gentlemen.

 

[McGu]

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.

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?

 

[Jonmo1]

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.