VBA not finding named ranges

[Jimij]

Hi, this is my first post and I really hope someone can help me out. My workbook has a number of sheets, each with a WorkBook Level Named Range. The following formula on Sheet2 accesses data from the named ranges on Sheet4 (LOData) and Sheet5 (SLOData) and does what it is supposed to do when typed directly into the target cell on Sheet2:

=IF(D2="",SUM(HLOOKUP(B2,LOData,14,FALSE),HLOOKUP(B2,LOData,15,FALSE)),SUM(HLOOKUP(D2,SLOData,11,FALSE),HLOOKUP(D2,SLOData,12,FALSE)))

however when I try to use VBA to insert the formula, I get an error message that an Object has not been defined. I have tried defining LOData and SLOData as Ranges and Names and even setting the ranges, without success. MyFormula is defined as a String

My appreciation in advance.

 

[StephenCrump]

Welcome to the Forum!

You need to double up any quotation marks inside other quotation marks:

MyFormula = "=IF(D2="""",SUM(HLOOKUP(B2,LOData,14,FALSE),HLOOKUP(B2,LOData,15,FALSE)),SUM(HLOOKUP(D2,SLOData,11,FALSE),HLOOKUP(D2,SLOData,12,FALSE)))"

 

[Jimij]

Hi Stephen, Thanks for the prompt response - works like a charm. Extremely grateful for the advice. Best wishes to you and family for the Festive Season.

 

[StephenCrump]

Great, I'm glad it worked for you.