Indirect formula VBA

[Partjob]

Hi
I have a problem using the function Indirect within VBA. I am trying to insert the formula below using VBA. I know why I have the problem. The apostrophes are causing the formula to comment out. I don't know how to get around this if you can at all.
I have tried replacing the apostrophe with a different character to then find and replace later, I could not get this to work. the sheet name in in Cell ("C4") and I then reference it in the formula to get the result.
There does not seem to be indirect within VBA.
I don't really know another way I can return the result using adifferent method.

Sheet1.Range("E7").Formula = "=IF(C4="",TODAY()-1,INDEX(INDIRECT("'" & C40 & "'!$A$5:$BC$43"),MATCH(A4,INDIRECT("'" & C4 & "'!$A$5:$A$43"),0),49))"

Thanks as usual for your help, this seems simple but I been pondering this for over a day now and need direction.
Partjob

 

[Norie]

This will work, ie the code won't error, but I've no idea if it'll produce the formula you want.

Sheet1.Range("E7").Formula = "=IF(C4="""",TODAY()-1,INDEX(INDIRECT(""'"" & C40 & ""'!$A$5:$BC$43""),MATCH(A4,INDIRECT( ""'"" & C40 & ""'!$A$5:$A$43""),0),49))"

The formula that is in E7 looks like this.

=IF(C4="",TODAY()-1,INDEX(INDIRECT("'" & C40 & "'!$A$5:$BC$43"),MATCH(A4,INDIRECT( "'" & C40 & "'!$A$5:$A$43"),0),49))

 

[snb_]

Sheet1.Range("E7").Formula = replace("=IF(C4=##,TODAY()-1,INDEX(INDIRECT(' & C40 & '!$A$5:$BC$43#),MATCH(A4,INDIRECT(' & C4 & '!$A$5:$A$43#),0),49))","#",chr(34))

 

[pgc01]

Hi

To write a double quote inside a double quoted string you have to double it. So, after:

s= "This ""is"" as test"

s has the value

This "is" as test

In your case you are missing a lot of double quotes. I think you want:

Sheet1.Range("E7").Formula = "=IF(C4="""",TODAY()-1,INDEX(INDIRECT(""'" & C40 & "'!$A$5:$BC$43""),MATCH(A4,INDIRECT(""'" & C4 & "'!$A$5:$A$43""),0),49))"

For C40 = "Sheet2" and C4 = "Sheet3", you get

=IF(C4="",TODAY()-1,INDEX(INDIRECT("'Sheet2'!$A$5:$BC$43"),MATCH(A4,INDIRECT("'Sheet3'!$A$5:$A$43"),0),49))

 

[Partjob]

Norrie
Not for the first time you have got me out of a jam, you were correct about the function not working but I have solved that, I did know about double quoteing. So simple really.
Thank you
Partjob