VBA - Named Range Solution

[ragnar12]

I'm going through someone else's code at a new job and I CAN NOT get this line of vba to run. It looks right, but I'm obviously missing something....maybe a setting within VBA or excel that is turned off.

Any help or suggestions would be greatly appreciated. Thank you in advance.

Line erroring out:

MRIBFwb.Names.Add Name:="TeamofThree", RefersTo:=.Range("A2", .Range("A2").End(x1Down))

Line within surrounding code:

'Create Support tab to hold lists for drop-down validation
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Support"
Set Supportws = Sheets("Support")
With Supportws
    .Range("A1").Value = "Team of 3"
    .Range("B1").Value = "Leasing Manager"
    .Range("C1").Value = "Market"
    .Range("D1").Value = "SPP/Non-SPP"
    .Range("D2").Value = "SPP & Non"
    .Range("D3").Value = "SPP"
    .Range("D4").Value = "Non-SPP"
    .Range("E1").Value = "Share"
    .Range("E2").Value = "Consol"
    .Range("E3").Value = "At-Share"
    Occ_Summws.Range("C6", "E" & Occ_SummLR).Copy .Range("A2")
    .Range("A1", .Range("A1").End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:=xlYes
    .Range("B1", .Range("B1").End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:=xlYes
    .Range("C1", .Range("C1").End(xlDown)).RemoveDuplicates Columns:=Array(1), Header:=xlYes
    .Range("A1", .Range("A1").End(xlDown)).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
    .Range("B1", .Range("B1").End(xlDown)).Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
    .Range("C1", .Range("C1").End(xlDown)).Sort Key1:=.Range("C1"), Order1:=xlAscending, Header:=xlYes
    .Range("A2").Insert xlShiftDown
    .Range("A2").Value = "All Teams"
    .Range("B2").Insert xlShiftDown
    .Range("B2").Value = "All Lease Mgrs"
    .Range("C2").Insert xlShiftDown
    .Range("C2").Value = "All Mkts"
    .Range("A1:E1").Font.Bold = True
    MRIBFwb.Names.Add Name:="TeamofThree", RefersTo:=.Range("A2", .Range("A2").End(x1Down))
    MRIBFwb.Names.Add Name:="LeaseMgr", RefersTo:=.Range("B2", .Range("B2").End(x1Down))
    MRIBFwb.Names.Add Name:="Market", RefersTo:=.Range("C2", .Range("C2").End(x1Down))
    MRIBFwb.Names.Add Name:="SameStore", RefersTo:=.Range("D2", .Range("D2").End(x1Down))
    MRIBFwb.Names.Add Name:="Share", RefersTo:=.Range("E2", .Range("E2").End(x1Down))
End With

P.S. MRIBFwb is the workbook name (Set MRIBFwb = ThisWorkbook) at the top of this very lengthy code.

 

[JEC]

What if your write it like this.

.Range("A2", .Range("A2").End(x1Down)).Name = "TeamofThree"

 

[Fluff]

It should be xlDown, not x1Down, ie a lower case L not the number 1

 

[JEC]

Very sharp!

 

[JEC]

Some advice:
If you want to fill succeeding cells with text you could use method below

.Range("A1").Resize(, 5) = Array("Team of 3", "Leasing Manager", "Market", "SPP/Non-SPP", "Share")
.Range("D2").Resize(3) = Application.Transpose(Array("SPP & Non", "SPP", "Non-SPP"))
.Range("E2").Resize(2) = Application.Transpose(Array("Consol", "At-Share"))

instead of this

.Range("A1").Value = "Team of 3"
    .Range("B1").Value = "Leasing Manager"
    .Range("C1").Value = "Market"
    .Range("D1").Value = "SPP/Non-SPP"
    .Range("D2").Value = "SPP & Non"
    .Range("D3").Value = "SPP"
    .Range("D4").Value = "Non-SPP"
    .Range("E1").Value = "Share"
    .Range("E2").Value = "Consol"
    .Range("E3").Value = "At-Share"

In this case it is not completely necessary but if you have more cells to fill nex to each other, you should use an array

 

[ragnar12]

It should be xlDown, not x1Down, ie a lower case L not the number 1

I know it does look at a 1, but its an L. (I tried changing it just to confirm)

@JEC your solution of retyping it also didn't work.

I'm convinced it has to be a setting of some kind that is off....I'm totally lost for solutions at this point.

 

[Fluff]

The code you posted is definitely a one & not an L.
In what way doesn't the code work? Do you get any error messages?

 

[JEC]

Try removing all existing names first

 

[ragnar12]

I closed the workbook and reopened it correcting the code of L and 1 for all lines. That seemed to fix it.

Thank you guys!

 

[Fluff]

Glad it's sorted & thanks for the feedback.