VBA with XLOOKUP using different Tables

[gberg]

I am trying to come up with a VBA formula that pulls table names from different tables based on other criteria. I can get the table names but I am having issues making the name work with a XLOOKUP formula.

Here is a formula where I just use the table name itself

ActiveCell.FormulaR1C1 = _
"=XLOOKUP([@[PROJ '#]],Table71[PROJ '#],Table71[TOTAL JGP],""N/A"",0)"

Here is what I have tried

'Get Previous Month Table Name
Sheets(PM_tab).Select
Range("A2").Select
Dim TableName_PM_JPR As ListObject
Set TableName_PM_JPR = ActiveCell.ListObject
Sheets(JPR_tab).Select
ActiveCell.FormulaR1C1 = _
"=XLOOKUP(RC[-24],TableName_PM_JPR[PROJ '#],TableName_PM_JPR[TOTAL JGP],""N/A"",0)"


The macro throws an error on the XLOOKUP formula. I know the table name "TableName_PM_JPR" is correct, I tested by pasting the value in a worksheet. Just not sure what I am doing wrong.

Thanks for any help

 

[Peter_SSs]

Untested, but try

ActiveCell.FormulaR1C1 = _
  Replace("=XLOOKUP(RC[-24],#[PROJ '#],#[TOTAL JGP],""N/A"",0)", "#", TableName_PM_JPR)

 

[gberg]

I get an End of Statement error

 

[Peter_SSs]

Oops, I should not have chosen # as my placeholder.

Also difficulties of not having the full code or the sheet/table setup that you have! ?

I was also misled by the variable TableName_PM_JPR which at a glance I thought would hold the table name but it actually holds the ListObject itself.
Further, I did not deal correctly with the table headings that include the single quote mark and the # ([PROJ '#])

Try this instead.

ActiveCell.FormulaR1C1 = _
  Replace(Replace("=XLOOKUP([@[PROJ '#]],%[PROJ '#],%[TOTAL JGP],""N/A"",0)", "%", TableName_PM_JPR.Name), "'#", "'''#")

 

[gberg]

Peter,

Here is the full macro (I cut off the elseif statements after January, but gives you the main idea). Still getting an error

 

[Peter_SSs]

When posting code, please post the actual code so that it can be copied to test with. Use the available code tags. My signature block below has more details
Posting a picture to show error message & problem line is fine though.

When copying code from the forum to your workbook, use the 'Copy to clipboard' icon at the top right of the code ..

.. and then you won't miss parts of the code. That red line looks very little like what I posted in post #4

 

[gberg]

Peter, thanks for being kind with my shortcomings, still learning how to best present my questions so appreciate you giving advice. I'm still getting errors even with the ending parenthesis. I hope I am doing this correctly

Sub PM_Info()

    'Get Current Sheetname
    JPR_tab = ActiveSheet.Name
    
    'Get Corresponding JPR Sheet
    If JPR_tab = "Dec JPR (PY)" Then
        WS_tab = "WS Dec (PY)"
    ElseIf JPR_tab = "Jan JPR" Then
        WS_tab = "WS Jan"
        PM_tab = "Dec JPR (PY)"
    ElseIf JPR_tab = "Feb JPR" Then
        WS_tab = "WS Feb"
    ElseIf JPR_tab = "Mar JPR" Then
        WS_tab = "WS Mar"
    ElseIf JPR_tab = "Apr JPR" Then
        WS_tab = "WS Apr"
    ElseIf JPR_tab = "May JPR" Then
        WS_tab = "WS May"
    ElseIf JPR_tab = "Jun JPR" Then
        WS_tab = "WS Jun"
    ElseIf JPR_tab = "Aug JPR" Then
        WS_tab = "WS Aug"
    ElseIf JPR_tab = "Sep JPR" Then
        WS_tab = "WS Sep"
    ElseIf JPR_tab = "Oct JPR" Then
        WS_tab = "WS Oct"
    ElseIf JPR_tab = "Nov JPR" Then
        WS_tab = "WS Nov"
    ElseIf JPR_tab = "Dec JPR" Then
        WS_tab = "WS Dec"
    End If


    'Calulate Previous JGP
    'Get Previous Month Table Name
    Sheets(PM_tab).Select
    Range("A2").Select
    Dim TableName_PM_JPR As ListObject
    Set TableName_PM_JPR = ActiveCell.ListObject
    Sheets(JPR_tab).Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP([@[PROJ '#]],%[PROJ '#],%[TOTAL JGP],""N/A"",0)", "%", TableName_PM_JPR.Name), "'#", "'''#")
    'Range("AA3").Value = TableName_PM_JPR

End Sub

 

[gberg]

Here is a small version of the table. There is another table with Total JGP that I am trying to pull data from

PROJ #	PROJECT NAME	TOTAL COST	INCOME	TOTAL JGP	% ON REVENUE	PREVIOUS JGP
7D7389	Henderson Hospital Tower Sr Construction	$5,020	$5,166	-[@[TOTAL COST]]]$146	]/[@INCOME]]2.83%

 

[gberg]

Work in progress, so before you ask I am working on the "January" tab and this PM_tab = "Dec JPR (PY)" will need to be updated for the following months. I was just trying to get January to work first

 

[Peter_SSs]

I'm still getting errors even with the ending parenthesis.

You may not have noticed but I edited my post (must have been after you saw it) to remove that comment because that was not all that was missing from the code I posted.
Did you read the part about the best way to copy my code?

Having said that, my previous code still will not work because I have now seen some of your table headings and I was misinterpreting your code in relation to that.
Try this instead.

ActiveCell.FormulaR1C1 = _
  Replace("=XLOOKUP([@[PROJ '#]],%[PROJ '#],%[TOTAL JGP],""N/A"",0)", "%", TableName_PM_JPR.Name)