Try the code below and follow the comments - basically the code opens each spreadsheet in the given folder, loops through the sheets in that workbook, runs your COUNTIF formula for each sheet and keeps a record of the total count.
Option Explicit Sub CheckForValue() Dim objFso As FileSystemObject '<-- add Microsoft Scripting Runtime as a reference Dim objFile As File Dim wbToCheck As Workbook Dim wsToCheck As Worksheet Dim strPath As String Dim varValue As Variant Dim lngValueCount As Long Dim lngTotal As Long Dim wsf As WorksheetFunction On Error Goto CleanUp strPath = "H:\Macro\positions" Set objFso = New FileSystemObject '<-- access to file system varValue = 288 '<-- value you are looking for lngTotal = 0 '<-- total count of value you are looking for Set wsf = Application.WorksheetFunction '<-- shortcut to WorksheetFunction ' iterate files in folder For Each objFile In objFso.GetFolder(strPath).Files ' only check spreadsheets If objFile.Type = "Microsoft Excel Worksheet" Then ' get reference to workbook Set wbToCheck = Workbooks.Open(objFile.Path) ' iterate worksheets For Each wsToCheck In wbToCheck.Worksheets ' your original formula lngValueCount = wsf.CountIf(wsToCheck.Range("F:F"), varValue) ' add to total lngTotal = lngTotal + lngValueCount Next wsToCheck ' close without saving changes wbToCheck.Close SaveChanges:=False End If Next objFile ' final count of value you are looking for Debug.Print "Total is: " & lngTotal CleanUp: ' error handling If Err.Number <> 0 Then Debug.Print Err.Description End If Set objFile = Nothing Set objFso = Nothing End Sub
Based on your comment that The operation has to be done just once, I don't mind waiting some hours for it to finish then the above code will do that, just grinding through sheets checking for the value. If you want to improve the speed you can use the following code before the For loop to help:
Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False
And then afterward turn the settings back (after the CleanUp: statement):
Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True
