CounIf loop throught closed workbooks VBA value

[AlbocIDO]

I'm trying to loop through closed workbooks in order to count the presence of a single person in a location monthly.
In the Countif it gives me type mismatching error, probably referring to the first range argument, that in this case is a string.
If it is the question, how can i arrange the procedure?

Option Explicit

Public Function ContMonth(colleague As Range, location As Range, month As Range) As Long

Dim nameMonth As String
Dim nameLocation As String
Dim nameColleague As String
Dim rangeLocation As String
Dim stringMonth As String
Dim file As Variant
Dim count As Integer
        
nameMonth = month
nameLocation = location
nameColleague = colleague

Select Case True
    Case nameLocation = "ponte milvio"
        rangeLocation = "$A$2:$B$2"
    Case Else
        rangeLocation = "null"
End Select
        
Select Case True
    Case nameMonth = "January"
        stringMonth = "-01-2022"
    Case Else
        stringMonth = "null"
End Select
file = Dir("C:\Users\sbalo\Desktop\Test\*.xlsx")

While (file <> "")
    Do While InStr(file, stringMonth) > 0
        count = count + Application.CountIf("C:\Users\sbalo\Desktop\Test\" & "[" & file & "]" & "Sheet1" & "'" & "!" & rangeLocation, nameColleague)
    file = Dir()
    Loop
Wend
End Function

Sub Test()
Dim counter As Long
Dim name As Range
Dim location As Range
Dim month As Range
Set name = Range("A3")
Set location = Range("B2")
Set month = Range("B1")

    counter = ContMonth(name, location, month)
End Sub

 

[GlennUK]

Countif doesn't work on closed workbooks. You need to either open each workbook you are examining before you do the countif, or use some other method.