Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

VBA Excel 2007 name ranges

Avatar of StierInvest
StierInvest asked on
ProgrammingMicrosoft Excel
15 Comments1 Solution759 ViewsLast Modified:
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,
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""")
            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
            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.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"
                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)
Avatar of StierInvest

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 15 Comments.
See Answers