I have the following formula in a workbook, using the INDIRECT function to refer to another workbook named "2014 Tax Year PACs.xlsx". INDIRECT is pointing to a cell in the first workbook with the current tax year so I don't need to edit formulas each time the tax year changes.
Following is the INDIRECT function as it appears in the cell in the first workbook mentioned above: =COUNTIF(INDIRECT("'[" & UT_current_tax_year & " Tax Year PACs.xlsx]Sheet1'!$D:$D"),">""")-1. It returns an accurate count of entrties in the second workbook with no indications of any errors.
However, when I insert the formula into the same cell using VBA, with the only changes being doubling up on interior quotation marks, I get a runtime error 1004: "Application-defined or object-defined error." Below is the formuia as it appears in the line of code meant to insert it into the cell in the first workbook:
Selection.Formula = "=COUNTIF(INDIRECT(""'["" & UT_current_tax_year & "" Tax Year PACs.xlsx]Sheet1'!$D:$D""),"">"""")-1"
This one has me stumped, so I'd really appreciate some help.