I'm trying to code this piece:

..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A6,INDIRECT("
'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT("'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE))"

But the single quote (') in the INDIRECT("' is causing everything after that
to be treated like a comment. How do I get the code to treat it just like a
regular character and not a comment?

It's not the apostrophe, it's the incorrect number of double quotes:

.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&Y2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A6,INDIRECT(""'[""&Y2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

Remember to double up all of the double quotes.

And remember that if you're going to use =indirect(), then the sending workbook
needs to be open--otherwise, you'll see a #REF! error.

Bishop wrote:

I'm trying to code this piece:

.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A6,INDIRECT("
'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT("'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE))"

But the single quote (') in the INDIRECT("' is causing everything after that
to be treated like a comment. How do I get the code to treat it just like a
regular character and not a comment?

--

Dave Peterson

It looks like you copied the statement from a posting. You have to put the
data instruction in one line of do this. I remove some of your double quotes
because they were in the wrong locations.

..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A6,INDIRECT(" & _
"'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000),11,FALSE)),""""," & _
"VLOOKUP($A6,INDIRECT('["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000),11,FALSE))"


"Bishop" wrote:

I'm trying to code this piece:

.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A6,INDIRECT("
'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT("'["&Y2&"]By_Rep_by_Filter'!$F$1:$P$5000"),11,FALSE))"

But the single quote (') in the INDIRECT("' is causing everything after that
to be treated like a comment. How do I get the code to treat it just like a
regular character and not a comment?