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?