VBA - Named Range Solution

ragnar12

Board Regular
Joined
May 1, 2013
Messages
116
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:
VBA Code:
MRIBFwb.Names.Add Name:="TeamofThree", RefersTo:=.Range("A2", .Range("A2").End(x1Down))

Line within surrounding code:
VBA 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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
What if your write it like this.

VBA Code:
.Range("A2", .Range("A2").End(x1Down)).Name = "TeamofThree"
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
It should be xlDown, not x1Down, ie a lower case L not the number 1
 
Solution

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
Very sharp!
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
.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

VBA Code:
.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

Board Regular
Joined
May 1, 2013
Messages
116
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 21, 2021
Messages
797
Office Version
  1. 365
Platform
  1. Windows
Try removing all existing names first
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
116
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,700
Messages
5,766,010
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top