Add Names to Multiple Cells using Array

[dturgel]

I'd like to define names for 52 cells and do it with one array. I recorded the naming of one range and learned the .Names.Add Name:= notation so I'm wondering can that be combined with an array. I'm getting a 438 object error when I try the following - any ideas?

Sub TestNameArray()
With ActiveWorkbook.Sheets("Summary FOT")
                        .Range("BH124:DG124").Names.Add Name:=Array("MCTSI2AKNewImportL1", "MCTSI2ALNewImportL1", "MCTSI2ARNewImportL1", "MCTSI2AZNewImportL1", "MCTSI2CANewImportL1", "MCTSI2CONewImportL1", "MCTSI2CTNewImportL1", "MCTSI2DCNewImportL1", "MCTSI2DENewImportL1", "MCTSI2FLNewImportL1", "MCTSI2GANewImportL1", "MCTSI2HINewImportL1", "MCTSI2IANewImportL1", "MCTSI2IDNewImportL1", "MCTSI2ILNewImportL1", "MCTSI2INNewImportL1", "MCTSI2KSNewImportL1", "MCTSI2KYNewImportL1", "MCTSI2LANewImportL1", "MCTSI2MANewImportL1", "MCTSI2MDNewImportL1", "MCTSI2MENewImportL1", "MCTSI2MINewImportL1", "MCTSI2MNNewImportL1", "MCTSI2MONewImportL1", "MCTSI2MSNewImportL1", "MCTSI2MTNewImportL1", "MCTSI2NCNewImportL1", "MCTSI2NDNewImportL1", "MCTSI2NENewImportL1", "MCTSI2NHNewImportL1", "MCTSI2NJNewImportL1", "MCTSI2NMNewImportL1", "MCTSI2NVNewImportL1", "MCTSI2NYNewImportL1", "MCTSI2OHNewImportL1", "MCTSI2OKNewImportL1", "MCTSI2ORNewImportL1", "MCTSI2PANewImportL1", "MCTSI2RINewImportL1", "MCTSI2SCNewImportL1", _
                        "MCTSI2SDNewImportL1", "MCTSI2TNNewImportL1", "MCTSI2TXNewImportL1", "MCTSI2UTNewImportL1", "MCTSI2VANewImportL1", "MCTSI2VTNewImportL1", "MCTSI2WANewImportL1", "MCTSI2WINewImportL1", "MCTSI2WVNewImportL1", "MCTSI2WYNewImportL1", "MCTSI2oTHERNewImportL1")
End With
End Sub

 

[Sixthsense]

Simple question, why you would like to create 52 different names for a SINGLE RANGE?

 

[Jerry Sullivan]

Simple question, why you would like to create 52 different names for a SINGLE RANGE?

@Sixthsense, I'm pretty sure that the OP wants to create 52 different names- one for each cell in the 52 cells of .Range("BH124:DG124").

@dturgel, You can't use that syntax to create those names in one step from the array. You could:
1. step through each element of the array defining each name using a loop. or
2. if the cells above or below the range to be named are empty, you could temporarily write your array to those cells then use the Range.CreateNames method.

 

[Sixthsense]

@Sixthsense, I'm pretty sure that the OP wants to create 52 different names- one for each cell in the 52 cells of .Range("BH124:DG124")

Oopss... thanks for correcting me. I have not seen it in that way

 

[dturgel]

Jerry, thanks!

I found the Range.CreateNames method explanation here:
https://msdn.microsoft.com/en-us/library/office/ff192960.aspx

For some reason the bottom is not working - am I missing something? In the following code, cell A8 is not getting named even though cell A9 has a value in it - what do you think?

Sub CreateNamesBottom()
Set rangeToName = Worksheets("Sheet1").Range("A8:C9")
rangeToName.CreateNames Bottom:=True
End Sub

 

[Jerry Sullivan]

For some reason the bottom is not working - am I missing something? In the following code, cell A8 is not getting named even though cell A9 has a value in it - what do you think?

I've used that method manually before, but not through VBA.

I was able to replicate the problem you describe, and it appears to be due to the Excel trying to use both the Bottom Row and Left Column to define the names. That shouldn't happen, since the documentation says that the default for the Optional Parameters is False.

Nonetheless, the code seems to work consistently by explicitly providing arguments for the Optional Parameters like this

 Dim rangeToName As Range
 
 Set rangeToName = Worksheets("Sheet1").Range("A8:C9")
 '--don't show warnings if names already exist
 Application.DisplayAlerts = False
 rangeToName.CreateNames Top:=False, Left:=False, Bottom:=True, Right:=False
 Application.DisplayAlerts = True

 

[dturgel]

Jerry, sorry I missed this - thanks so much!

Daniel