Is it possible to do a countif in VBA using a closed workbook?

[Sinon]

Hi all. My current dilemma is this: I am writing a macro that will do a few things but I am stuck at having to do a countif using a range from a different workbook. Now this workbook is a binary one (xlsb not xlsx) and is password protected. It has multiple sheets as well.

What I want to do is a countif using a value from my workbook (the one with the code in it) as the criteria and using a column from a sheet from the closed xlsb workbook for my range:

COUNTIF(Range,Value)
Range = column D from a sheet in the closed workbook that is password protected
Value= cell from current open workbook

I am using WorksheetFunction.CountIf. I only need the range, which i tried setting up but I am getting runtime error 9 "Subscript out of range". I used a range variable MyRange and tried referencing the workbook with

Set MyRange=Workbooks("drive:\Folder1\Folder2\WorkbookName.xlsb").Worksheets("Sheet4").Range("D:D")

So, is it possible to do a countif using this closed, password protected workbook? And if so, how do I reference the workbook?

Thank you all

 

[chicagocomputerclasses]

SUMPRODUCT works for closed workbooks.

That being said, if it's password protected you will most likely need to open and unprotect the workbook before you can do any formulas out of it.

 

[Sinon]

SUMPRODUCT works for closed workbooks.

That being said, if it's password protected you will most likely need to open and unprotect the workbook before you can do any formulas out of it.

Hi and thanks for your reply. The workbook is not protected but there is a password to open it. The password was added through the Save As window by going to Tools->General Option -> Password to open. Does your answer still apply in this case?

 

[chicagocomputerclasses]

Yes, see here
http://www.mrexcel.com/forum/excel-...sual-basic-applications-another-workbook.html

 

[Sinon]

Thank you, will open it and use countif that way then.