CODEWITHSUNDEEP
vbaexcel
Maciej
Maciej

Reputation: 47

Named range with adress from another named range

I have code like below, I would like to add named range which adress is connected with other named ranges, below code does not work, thanks in advance for all ideas. 

ActiveWorkbook.Names.Add _
        Name:="somerange", _
        RefersTo:="=Sheet1!Cells(Range("namedrange1").row-5,Range("namedrange2").column)"

Upvotes: 0

Views: 137

Answers (2)

user4039065
user4039065

Reputation:

With a named range namedrange1 defined as RefersTo:=Sheet2!$B$7 with workbook scope and a named range namedrange2 defined as RefersTo:=Sheet3!$D$10 with workbook scope, this will define a new named range named somerange with RefersTo:=Sheet1!$D$2 with workbook scope.

With ActiveWorkbook
    With .Worksheets("Sheet1")
        With .Cells(Range("namedrange1").Row - 5, Range("namedrange2").Column)
            .Name = "somerange"
        End With
    End With
End With

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Providing namedrange1 is a defined named range you just write it as you would a normal formula:

ActiveWorkbook.Names.Add "somerange", "=OFFSET(Sheet1!NamedRange1,-5,0,1)"

In your code you could be referencing two separate sheets - Sheet1.Cells and ActiveSheet.Range - if you don't explicitly refer to the sheet name it will use the active sheet. You're effectively writing: "=Sheet1!Cells(ActiveSheet.Range("namedrange1").row-5,ActiveSheet.Range("namedrange2").column)"

Edit: Also your code seems to be a combination of formula and VBA code - Sheet1! is used to refer to another sheet in a formula, Cells is a VBA type reference as is .Row and .Column

Edit 2: You could also use: ActiveWorkbook.Names.Add "SomeOtherRange", "=" & Sheet1.Range("NamedRange1").Offset(-5).Resize(1).Address but this will hardcode the address - when you check your named ranges it will look something like: =Sheet1!$I$5 (if your named range started in cell $I$10).

Upvotes: 1

Related Questions