The code below runs a function to get data from closed workbooks. Where it specifies range "AD:AD" can i put a formula in to count if range AD contains certain values??
cValue = GetInfoFromClosedFile(FolderName, wbList(i), wsName, ("AD:AD"))
Cells(r, 2).Formula = cValue
Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbname As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbname) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbname & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
cValue = GetInfoFromClosedFile(FolderName, wbList(i), wsName, ("AD:AD"))
Cells(r, 2).Formula = cValue
Private Function GetInfoFromClosedFile(ByVal wbPath As String, wbname As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbname) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbname & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function