We help IT Professionals succeed at work.

VBA Excel 2007 name ranges

StierInvest
StierInvest used Ask the Experts™
on
Hi Experts,

I'm having trouble naming the range. The program rus but is does not name the range "myDates"
Can someone please hep. I tried the other solution on the websites but no luck.

Thank a mil,

Best Regards,
StierInvest
Sub OptimizationEx()

Dim myBook As Workbook
Dim mySheet, mySheet1, mySheet2, myShee3 As Worksheet
Dim myRange, myRange1, myDates, myFunds As Range


Set myBook = ActiveWorkbook
On Error Resume Next
Set mySheet = Sheets("Database") 'mySheet is Database
'On Error GoTo 0

        If mySheet Is Nothing Then 'If there is no sheet with the name database
                
             MsgBox ("Please change your data sheet name to ""Database""")
                
        Else
            
            'Optimization
            mySheet.Range("B2").Select
            ActiveWindow.FreezePanes = True

            
            On Error Resume Next
            Application.DisplayAlerts = False
            Sheets("Optimization Temp").Delete
            Application.DisplayAlerts = True
            Set mySheet1 = Worksheets.Add
            mySheet1.Name = "Optimization Temp" 'MySheet1 is Optimization sheet
            mySheet.Range("A1").CurrentRegion.Copy mySheet1.Range("A1")
            Application.CutCopyMode = False
            mySheet1.Range("B2").Select
            ActiveWindow.FreezePanes = True
            Set myRange = mySheet1.Range("A1").CurrentRegion 'myRange is the range with the Funds & optimization constraints
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Set myRange = myRange.Offset(-4, 0).Resize(myRange.Rows.Count + 4, myRange.Columns.Count + 1)
            myRange.Select
            
            myRange.Columns(1).Rows(1).Formula = "Minimum Weight"
            myRange.Columns(1).Rows(2).Formula = "Maximum Weight"
            myRange.Columns(1).Rows(3).Formula = "Optimal Weight"
            myRange.Columns(1).Rows(4).Formula = "Fund number"
            myRange.Columns(1).Rows(5).Formula = "Fund name"
            Columns("A:A").EntireColumn.AutoFit
            
                For i = 1 To myRange.Columns.Count - 2
                
                        myRange.Rows(4).Columns(i + 1).Formula = i
                        myRange.Rows(4).Columns(i + 1).NumberFormat = "#####0"
                Next i
                
            Set myDates = myRange.Offset(5, 0).Resize(myRange.Rows.Count - 5, 1).Select
            
            ' I can't get the Range "myDates" to be called Dates
            ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.myDates.Address(False, False)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
Try:

            ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.Name & "'!" & myDates.Address
Top Expert 2008
Commented:
try replacing

ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.myDates.Address(False, False)

by

ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.name & "'!" & mySheet1.myDates.Address(False, False)

Thomas

Author

Commented:
Thank you for the quick response but no luck.
Any other ideas?
Top Expert 2008

Commented:
IF you add a On error goto 0 line before the names line, do you get an error? If yes, what?

If you try hard coding the address of the named range, does it work?

Do you have the inverted commas before and after the sheet name?

Thomas

Author

Commented:
Hi Thomas,

There was something wrong with the range "myDates". (sorry my bad). i've managed to fix it. If i put "on error goto 0" a line before nothing happens. It still accepts the statement. How can i hard code the address?

Yes i do have inverted commas before and after.

Thanks,
StietInvest
Top Expert 2008
Commented:
well, the following line has variables

ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.myDates.Address(False, False)

you can hardcode it using the range as you expect it.

eg:
msgbox mySheet1.myDates.Address(False, False)
would give you the range address, you can then plug it in your code as shown below.

ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='Optimization Temp'!A4:B10"

If it works, the issue is on the "='" & mySheet1.myDates.Address(False, False) part, if it doesn't the issue is elsewhere.

Thomas

Author

Commented:
Hi Guys,

I've managed to get it right. one needs to take out the "R1C1" in that statement. Now every time i select the named range it moves down all the time. Any idea why?

Sub OptimizationEx()

Dim myBook As Workbook
Dim mySheet, mySheet1, mySheet2, myShee3 As Worksheet
Dim myRange, myRange1, myDates, myFunds As Range


Set myBook = ActiveWorkbook
On Error Resume Next
Set mySheet = Sheets("Database") 'mySheet is Database


        If mySheet Is Nothing Then 'If there is no sheet with the name database
                
             MsgBox ("Please change your data sheet name to ""Database""")
                
        Else
            On Error GoTo 0
            'Optimization
            mySheet.Select
            mySheet.Range("B2").Select
            ActiveWindow.FreezePanes = True

            
            On Error Resume Next
            Application.DisplayAlerts = False
            Sheets("Optimization_Temp").Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
            
            Set mySheet1 = Worksheets.Add
            mySheet1.Name = "Optimization_Temp" 'MySheet1 is Optimization sheet
            mySheet.Range("A1").CurrentRegion.Copy mySheet1.Range("A1")
            Application.CutCopyMode = False
            mySheet1.Range("B2").Select
            ActiveWindow.FreezePanes = True
            Set myRange = mySheet1.Range("A1").CurrentRegion 'myRange is the range with the Funds & optimization constraints
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            mySheet1.Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Set myRange = myRange.Offset(-4, 0).Resize(myRange.Rows.Count + 4, myRange.Columns.Count + 1)
            myRange.Select
            
            myRange.Columns(1).Rows(1).Formula = "Minimum Weight"
            myRange.Columns(1).Rows(2).Formula = "Maximum Weight"
            myRange.Columns(1).Rows(3).Formula = "Optimal Weight"
            myRange.Columns(1).Rows(4).Formula = "Fund number"
            myRange.Columns(1).Rows(5).Formula = "Fund name"
            Columns("A:A").EntireColumn.AutoFit
            
                For i = 1 To myRange.Columns.Count - 2
                
                        myRange.Rows(4).Columns(i + 1).Formula = i
                        myRange.Rows(4).Columns(i + 1).NumberFormat = "#####0"
                Next i
                
            myRange.Select
            Set myDates = myRange.Offset(5, 0).Resize(myRange.Rows.Count - 5, 1)
            myDates.Select
            On Error GoTo 0
            ActiveWorkbook.Names.Add Name:="Dates", RefersTo:="='" & mySheet1.Name & "'!" & myDates.Address(False, False)
             
            Set myFunds = myRange.Offset(4, 1).Resize(1, myRange.Columns.Count - 2)
            myFunds.Select
            ActiveWorkbook.Names.Add Name:="Funds", RefersTo:="='" & mySheet1.Name & "'!" & myFunds.Address(False, False)

Open in new window

Top Expert 2008

Commented:
What do you mean by select the named range? in VBA or in the worksheet. And how does it move down?

Author

Commented:
It doesn't stay in the same place as soon as i select it in the worksheet.

The range is the same size but on a different place on the worksheet
Top Expert 2008

Commented:
you select a range using the mouse, then your selection changes?

Do you have a worksheet_Selectionchange event on your spreadsheet? Right-click the tab and View code to see.

Thomas

Author

Commented:
Hi Thomas,

No luck. as soon as i use RefersTo it works. But then as soon as i select a different cell on the Worksheet and then select the named range it moves to a different place. Meaning that recalculate the range according to the cell that is selected on the sheet

Thanks,
StierInvest
Top Expert 2008

Commented:
any way you can upload that sheet?


Hi Thomas,

Thanks for your time. I've managed to solve it

ActiveWorkbook.Names.Add Name:="Dates", RefersTo:="=" & mySheet1.Name & "!" & myDates.Address(True, True)

Looks like one needs to fix the cells, use RefersTo instead of RefersToR1C1 and leave out the '.

Does it make sense?

Thanks
StierInvest
Top Expert 2008

Commented:
I don't know if it makes sense but I'm glad it's working out for you.

Thomas
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you use RefersToR1C1, then you need to pass a reference in R1C1 format:


 ActiveWorkbook.Names.Add Name:="Dates", RefersToR1C1:="='" & mySheet1.myDates.Address(True, True, xlR1C1)

or you could just use:


mySheet1.myDates.Name = "Dates"