Quotation Marks in Formula

[bayles]

Hi,

This formula works when it is in the worksheet but when I try to transfer it to VBA I am missing something with the quotation marks etc.

Any assistance appreciated. I'm sure it is a quick fix for the right person.

Thanks
Ryan

"=IF(RC22="""","""",IF(HLOOKUP(RC22,INDIRECT("'CATEGORY HIERARCHY ("&RC33&")'!$1:$100",TRUE),R1C,FALSE)=0,"""",HLOOKUP(RC22,INDIRECT("'CATEGORY HIERARCHY ("&RC33&")'!$1:$100",TRUE),R1C,FALSE)))"

 

[RoryA]

You need to double up the quotes round the INDIRECT arguments too:

"=IF(RC22="""","""",IF(HLOOKUP(RC22,INDIRECT(""'CATEGORY HIERARCHY (""&RC33&"")'!$1:$100"",TRUE),R1C,FALSE)=0,"""",HLOOKUP(RC22,INDIRECT(""'CATEGORY HIERARCHY (""&RC33&"")'!$1:$100"",TRUE),R1C,FALSE)))"