Subscript out of range

[biafrapl]

Hi,

When I look at the Name Manager I can find a table called QUOTA_1, but when I reference it in VBA code I get the subscript out of range error. Have you encountered anything like that in the past?

I'm using Excel for Office 365

 

[Fluff]

Hi & welcome to MrExcel.
Is sheet "Quota" the active sheet when you run that code?

 

[biafrapl]

Yes it is

 

[Fluff]

How have you declared ws & tbl?

 

[biafrapl]

Yes I did.

I am using Microsoft Master Data Services to pull data from SQL to excel spreadsheet. While connecting to MDS the QUOTA_1 table is being created automatically. It seems that I have only issue with referencing this table.

 

[Fluff]

I have never used MDS & so cannot help any further.

 

[biafrapl]

Ok, thanks.

What is really weird is that when I have something like that:

Range("QUOTA_1[[#All],[Industry Growth]]").Select

Everything works perfectly. But when I try to execute the following code it fails and gives error:

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = ActiveSheet
Set tbl = ws.ListObjects("QUOTA_1")

 

[jasonb75]

Longwinded, but see if this works

Set tbl = ws.ListObjects(Range("Table4").ListObject.Name)

 

[biafrapl]

Worked!! Thanks!!