Using names workbooks in formulas

[Blunder1]

Hi,

I have named two workbooks wb1 and wb2 (the real name of wb2 will change depending on the date). I have also named the sheets ws and wt in wb1 and rd2 and rd3 in wb2. I then need to use formulas in wb1 that use use values in wb2, however, i'm struggling to use the workbook names in the formulas.

Below is one of my formulas, i need 'Excelsius Raw Data COB 0905.xls' to be wb2 and the sheet name 'cash' to be rd2.

ws.Range("c" & r).FormulaArray = "=SUM(('[Excelsius Raw Data COB 0905.xls]Cash'!" & strrng & "=$b$1)*('[Excelsius Raw Data COB 0905.xls]Cash'! " & strrng1 & "=7))"

Thanks in advance

Blunder

 

[VoG]

Try

ws.Range("c" & r).FormulaArray = "=SUM(('[" & wb2 & "]" & rd2 & "'!" & strrng & "=$b$1)*('[" & wb2 & "]" & rd2 & "'! " & strrng1 & "=7))"

 

[Andrew Poulsom]

Do you mean that you have assigned the workbooks and worksheets to object variables? If so try:

    ws.Range("c" & r).FormulaArray = "=SUM(('[" & wb2.Name & "]" & rd2.Name & "'!" & strrng & "=$b$1)*('[" & wb2.Name & "]" & rd2.Name & "'! " & strrng1 & "=7))"

 

[Blunder1]

Perfect! Thank you for your quick reply, Andrew