Syntax Question - application.worksheetfunction.countifs how to refer to another workbook in VBA

[Loun]

Hi All,

I managed to get this worksheetfunction.Countifs to work yesterday (Thanks to Jonmo for the solution!)

however, I now need to amend part of the Countifs formula so that the range is in another workbook. I can't seem to get the syntax right.

here is the formula



x = Application.Countifs(Range("$B$2:$B$96"),Cells(ActiveCell.Row,"B"),Range("$C$2:$C$96"),Cels(ActiveCell.Row,"C"))
If x > 1 Then

Basically the b2:b96 portion and the c2:c96 portion I need to refer to a separate workbook (called Test.xlsm). The rest of the formula would remain the same.

Can anybody help please? I've tried using the recorder but it uses R1C1 formula rather than application.worksheetfunction (as I want the calculation to be internal) and I've had no joy with it.

Many thanks,

Lou

 

[Loun]

*** Please Ignore - Now resolved ***