VBA variable table name with Xlookups

[Reba]

Hi! I have a report I run every week where I combine schedules with timecards to generate attendance records. I have been trying to update the code to structured references and have been successful except when using xlookups. I think the problem lies in how I define my table as a listobject variable, but I'm not sure how else to do this as my table name changes each week and the tables are on separate sheets. I have a working code that will do this in the R1C1 format, but for the sake of my sanity as I continue to update and evolve this document I would like to use structured references.

To make lookups easier I have created a helper col on both tables that combines the agent name and date which I have cleverly called NameDate. The agent schedules are broken down into rows of activity codes so I am trying to return the first start time of the day and the last end time of the day. I have tried so many variations of this code but can't seem to find one that will allow me to use a variable for a table name.

Although the table name changes weekly the sheet name remains the same. If there is a way to use the sheet name with structured col references that could be a viable option but nothing I have tried has worked so far.

Sub schedule_lookup()

Dim GS As ListObject
Dim TBG As ListObject
Dim Saturday As String
Dim GST As String
'Define last Saturday
Saturday = Date - Weekday(Date)

Set GS = Sheets("Agent Schedules").ListObjects("AS_" & Saturday)
Set TBG = Sheets("Time Block").ListObjects("TBG_" & Saturday)
GST = "AS_" & Saturday
   
With TBG
  
    With .ListColumns(15)
    .Name = "Scheduled Start"
    .DataBodyRange = Application.XLookup([@NameDate], GS.ListColumns("NameDate").Range, GS.ListColumns("Start Time").Range)
    End With
    
        'Here's a list of what hasn't worked:
        '"=XLookup([@NameDate], GST[NameDate], GST[Start Time])"
        '"=XLookup([@NameDate], GS.ListColumns(""NameDate""), GS.ListColumns(""Start Time""))"
        '"=XLookup([@NameDate], Range(GS.ListColumns(""NameDate"")), Range(GS.ListColumns(""Start Time"")))"
        '"=XLookup([@NameDate], GS.ListColumns(""NameDate"").range, GS.ListColumns(""Start Time"").range)"
        'This was my formula prior to table conversion where the last row of data was defined as a variable
        'Range("L2:L" & LastRowTB).FormulaR1C1 = "=xLOOKUP(RC[-7],'Agent Schedules'!C[-10],'Agent Schedules'!C[-2])"

    With .ListColumns(16)
    .Name = "Scheduled End"
    .DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]],GS[NameDate],GS[End Time],,0,-1)"
    End With
    '    This is the previous Scheduled End formula
    '    Range("M2:M" & LastRowTB).FormulaR1C1 = _
    '        "=XLOOKUP(RC[-8],'Agent Schedules'!C[-11],'Agent Schedules'!C[-2],,0,-1)"

End With

End Sub

 

[Akuini]

Although the table name changes weekly the sheet name remains the same.

Try using the index number.
For example if the table is the only listobject in the sheet then the index is 1.

Set GS = Sheets("Agent Schedules").ListObjects(1)

 

[JeffFinnan]

Hi! I have a report I run every week where I combine schedules with timecards to generate attendance records. I have been trying to update the code to structured references and have been successful except when using xlookups. I think the problem lies in how I define my table as a listobject variable, but I'm not sure how else to do this as my table name changes each week and the tables are on separate sheets. I have a working code that will do this in the R1C1 format, but for the sake of my sanity as I continue to update and evolve this document I would like to use structured references.

To make lookups easier I have created a helper col on both tables that combines the agent name and date which I have cleverly called NameDate. The agent schedules are broken down into rows of activity codes so I am trying to return the first start time of the day and the last end time of the day. I have tried so many variations of this code but can't seem to find one that will allow me to use a variable for a table name.

Although the table name changes weekly the sheet name remains the same. If there is a way to use the sheet name with structured col references that could be a viable option but nothing I have tried has worked so far.

Sub schedule_lookup()

Dim GS As ListObject
Dim TBG As ListObject
Dim Saturday As String
Dim GST As String
'Define last Saturday
Saturday = Date - Weekday(Date)

Set GS = Sheets("Agent Schedules").ListObjects("AS_" & Saturday)
Set TBG = Sheets("Time Block").ListObjects("TBG_" & Saturday)
GST = "AS_" & Saturday
  
With TBG
 
    With .ListColumns(15)
    .Name = "Scheduled Start"
    .DataBodyRange = Application.XLookup([@NameDate], GS.ListColumns("NameDate").Range, GS.ListColumns("Start Time").Range)
    End With
   
        'Here's a list of what hasn't worked:
        '"=XLookup([@NameDate], GST[NameDate], GST[Start Time])"
        '"=XLookup([@NameDate], GS.ListColumns(""NameDate""), GS.ListColumns(""Start Time""))"
        '"=XLookup([@NameDate], Range(GS.ListColumns(""NameDate"")), Range(GS.ListColumns(""Start Time"")))"
        '"=XLookup([@NameDate], GS.ListColumns(""NameDate"").range, GS.ListColumns(""Start Time"").range)"
        'This was my formula prior to table conversion where the last row of data was defined as a variable
        'Range("L2:L" & LastRowTB).FormulaR1C1 = "=xLOOKUP(RC[-7],'Agent Schedules'!C[-10],'Agent Schedules'!C[-2])"

    With .ListColumns(16)
    .Name = "Scheduled End"
    .DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]],GS[NameDate],GS[End Time],,0,-1)"
    End With
    '    This is the previous Scheduled End formula
    '    Range("M2:M" & LastRowTB).FormulaR1C1 = _
    '        "=XLOOKUP(RC[-8],'Agent Schedules'!C[-11],'Agent Schedules'!C[-2],,0,-1)"

End With

End Sub

While I could code this, I would like to avoid using code if possible.

Thanks,
Jeff

 

[Reba]

Try using the index number.
For example if the table is the only listobject in the sheet then the index is 1.

Set GS = Sheets("Agent Schedules").ListObjects(1)

Great tip on the index number for the listobjects, I had not considered that and it will help in another area of my code.

However, it doesn't work here. No matter how I write it, using a variable continues to return an error in the formula

 

[Reba]

While I could code this, I would like to avoid using code if possible.

Thanks,
Jeff

Thanks Jeff, you've been incredibly helpful.

 

[Akuini]

Try using the table name in formula

Dim tn As String
Set GS = Sheets("Agent Schedules").ListObjects(1)
tn = Sheets("Agent Schedules").ListObjects(1).Name

So this part:

.DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]],GS[NameDate],GS[End Time],,0,-1)"

should be:

.DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]]," & tn & "[NameDate]," & tn & "[End Time],,0,-1)"

 

[Reba]

Try using the table name in formula

Dim tn As String
Set GS = Sheets("Agent Schedules").ListObjects(1)
tn = Sheets("Agent Schedules").ListObjects(1).Name

So this part:

.DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]],GS[NameDate],GS[End Time],,0,-1)"

should be:

.DataBodyRange.Formula2R1C1 = "=Xlookup([@[NameDate]]," & tn & "[NameDate]," & tn & "[End Time],,0,-1)"

I got an "application-defined or object defined error" when I tried that.
Thanks for all your suggestions! They have both given me a few other ideas to try, I mean still none of it has worked but with enough trial and error eventually it will!