VBA: creating name for ListObject column range fails when it's in "A1"

ozgurluk

New Member
Joined
May 10, 2018
Messages
1
Hi,

I want to create a name for the data range of a given table column, using the syntax "Table[Column_name]", so that if the table moves, expands, shrinks, is renamed, or reorder columns (...), the range referred to by the name is still correct (per opposition to use the range address syntax like "$A$1:$A$4").

Weirdly, creating such a name perfectly works, except when the table column "starts" in cell A1.
Any idea for this ? Excel bug (oh my god :eek:) ?

See code hereafter:
Table1 is in "C1:C4", creating the name testname1 = Table1[Col1] works
Table2 is in "A1:A4", creating the name testname2 = Table2[Col2] FAILS, throwing a 1004 error (formula error)
if you modify Table2 to be created in "A2:A5" or "B1:B4", it works !!!!
What's wrong with A1 cell :devilish: ?


Code:
Sub testname()
 Dim oSh As Worksheet, oLo1 As ListObject, oLo2 As ListObject
 Dim refersTo As String
 Const nm1 As String = "testname1", nm2 As String = "testname2"
 Const addr1 As String = "C1:C4", addr2 As String = "A1:A4"
 
 'Create sheet and tables
 Set oSh = ActiveWorkbook.Sheets.Add(ActiveWorkbook.Sheets(1))
 'Set oSh = ActiveWorkbook.Sheets(1)
 oSh.Activate
 Range(addr1).Value = Application.Transpose(Array("Col2", "v1", "v2", "v3"))
 Range(addr2).Value = Application.Transpose(Array("Col1", "v1", "v2", "v3"))
 Set oLo1 = oSh.ListObjects.Add(xlSrcRange, Range(addr1), , xlYes)
 Set oLo2 = oSh.ListObjects.Add(xlSrcRange, Range(addr2), , xlYes)
 oLo1.Name = "Table1"
 oLo2.Name = "Table2"
 
 'If already exist, delete the names
 On Error GoTo err0:
 ActiveWorkbook.Names(nm1).Delete
 ActiveWorkbook.Names(nm2).Delete
err0:
 
 'Create the name for tables
 refersTo = "=" & oLo1.Name & "[" & oLo1.ListColumns(1).Range(1, 1) & "]"
 On Error Resume Next
 ActiveWorkbook.Names.Add Name:=nm1, Visible:=True, refersTo:=refersTo
 If Err.Number > 0 Then
  MsgBox "Could not create name for : " & refersTo & vbCrLf & Err.Number & ": " & Err.Description
 Else
  MsgBox "Name successfuly created for : " & refersTo
 End If
 On Error GoTo -1 'reset error if any
 
 refersTo = "=" & oLo2.Name & "[" & oLo2.ListColumns(1).Range(1, 1) & "]"
 On Error Resume Next
 ActiveWorkbook.Names.Add Name:=nm2, Visible:=True, refersTo:=refersTo
  If Err.Number > 0 Then
  MsgBox "Could not create name for : " & refersTo & vbCrLf & Err.Number & ": " & Err.Description
 Else
  MsgBox "Name successfuly created for : " & refersTo
 End If
 
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top