intersection operator in @ indirect formula

[mi_ena]

Hi All,

I'm using INDIRECT formula for sometime and I find it quite useful but recently I discovered a situation that gave me some headache.
I have the following situation, I'm trying to populate a range of cells using a macro with the following formula:
"=ADDRESS(MATCH(FALSE,ISBLANK(INDIRECT("'"&D2&"'!C:C")),0),4)"
Previously the formula appeared in the desired cell and result was shown but now the formula in the cell will look like this: 
"=ADDRESS(MATCH(FALSE,ISBLANK(@INDIRECT("'"&D2&"'!C:C")),0),4)", the result will be #N/A.
Is there a way to avoid this situation?
Apart from manually find & replace which in my scenario is adding extra time and the automated process that I'm using is getting a bit weird.

Much appreciated

[#2 · Accepted solution]

@mi_ena 

Use Formula2 instead of Formula to assign the formula:

Range("...").Formula2 = "=ADDRESS(MATCH(FALSE,ISBLANK(INDIRECT(""'""&D2&""'!C:C"")),0),4)"

[#3]

Thanks @Hans Vogelaar
It worked!