INDIRECT Formula Insertion Problem

skepticologist · Feb 17th 2015

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:

Code

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.

**S O** · Feb 17th 2015

Re: INDIRECT Formula Insertion Problem

You haven't doubled all of the quotes up at the end:

Code

Selection.Formula = "=COUNTIF(INDIRECT(""'["" & UT_current_tax_year & "" Tax Year PACs.xlsx]Sheet1'!$D:$D""),"">"""""")-1"

also, you could just assign the value from this directly to the cell:

Code

Selection.Value = Evaluate("COUNTIF(INDIRECT(""'["" & UT_current_tax_year & "" Tax Year PACs.xlsx]Sheet1'!$D:$D""),"">"""""")-1")

skepticologist · Feb 18th 2015

Re: INDIRECT Formula Insertion Problem

I owe you one, SO. I could've looked at the formula for another day and still might not have added those last two quotes.

**S O** · Feb 18th 2015

Re: INDIRECT Formula Insertion Problem

No a problem - it's usually easier to use alternatives to this:

Code

'// Use a constant/variable
Const qt As String = """" '// or - Dim qt As String: qt = """"
MsgBox qt & "text in quotes" & qt


'// Use the ASCII value
MsgBox Chr(34) & "text in quotes" & Chr(34)

INDIRECT Formula Insertion Problem

Participate now!

Share