Selected Answer
First, here is the code you have been so patient waiting for.
Function CountWork(ByVal Worker As String, _
ByVal Dat As Date) As Integer
' 039-4404
Const DirName As String = "F:\Shopping\Specialists\Master Copy\Daily list\Split files\Archive\"
Const Ext As String = "*.xlsm" 'File extension
Const WsName As String = "SHOPPING LISTING"
Const SrcClm As String = "BI" ' change as required
Const FirstDataRow As Long = 5 ' change as required
Dim Fun As Integer ' function return value
Dim NextFn As String ' name of the next file to process
Dim Wb As Workbook ' workbook(NextFn)
Dim Ws As Worksheet ' working tab
Dim Rng As Range ' BI:BJ
Dim C As Long ' translation of SrcClm
C = Columns(SrcClm).Column
NextFn = Dir(DirName & Ext)
Application.ScreenUpdating = False
Do While NextFn <> ""
Set Wb = Workbooks.Open(DirName & NextFn, ReadOnly:=True)
On Error Resume Next
Set Ws = Wb.Worksheets(WsName)
If Err = 0 Then
With Ws
Set Rng = .Range(.Cells(FirstDataRow, C), _
.Cells(.Rows.Count, C).End(xlUp).Offset(, 1))
Fun = Fun + Application.WorksheetFunction.CountIfs( _
Rng.Columns(1), Worker, Rng.Columns(2), Dat)
End With
End If
Application.DisplayAlerts = False
Wb.Close False
Application.DisplayAlerts = True
NextFn = Dir
Loop
Application.ScreenUpdating = True
CountWork = Fun
End Function
I didn't succeed avoiding the opening of files. Below please find a small procedure you can use for testing, as I did.
Sub RunCountWork()
' Dim Worker As String
' Dim Dat As Date
'
' Worker = "Andrew"
' Dat = #1/12/2020#
' Debug.Print CountWork(Worker, Dat)
Debug.Print CountWork(Range("A6").Value, Range("C3").Value)
End Sub
At first, I entered values against declared variables. Then I replaced the variables with cell references in the function call. Both methods work. Observe that the cell references are taken from the ActiveSheet (no worksheet specified).
The code takes the count using the COUNTIFS worksheet function on columns BI:BJ. A procedure to create the two columns from the one existing is contained in the attached workbook. I'm glad you decided to make this change. It will greatly facilitate your work going forward.
Now, as you will see, the function is slow, even with only 3 files to check as I had in my tests. You should design a system to minimize running the code repetitively. The workflow will determine the efficiency of the code's deployment. This subject clearly breaks the limits of what we can do here, in one thread. Please post a new question if you need assistance with deploying the code I provide here.
To give you a test of what's to come, please study cell formatting, especially Custom formats for dates. You will learn that you don't need your fields "First date" and "Last Date". Everybody knows which is the first day of January, or the last, and Excel knows it better even than most people. Accordingly, your plan should be that you enter "2020" in A3 and all dates in the sheet change to that year. To create a new year, you want to change only that one cell.
So, A2 has the formula =DATE(A3, 1, 1) with a custom cell format of "mmmm". [C2] = IF(month(A2+1) = month(A2),A2+1,"") with a custom cell format of "ddd". [C3] =C2, but a cell format like "d mmm yy" or "d/m/yyyy" as you have it now. C2:C3 can be copied all the way to the right (after you fix the problem I shall mention next) and down to C12:C13 and again to the right from there.
For A12 I recommend the formula =DateAdd("m", 1, A2), [A13] =A12 but with a custom cell format of "yyyy". A12:A13 can be copied down to the next month because the distance from one month to the next should be maintained as same (even if you have some blank rows as staff changes) or you look for some more demanding formulas to support copying with different staff strength.
With this system you would have the frist day of each month in column A. I don't know why you are interested in the last day but it can either be read from the last non-blank cell in row 2 or it can be calculated from the value in A2 with this formula =DateAdd("m",1,A2)-1.
My final salvo today is against the use of merged cells. Your design is far too demanding to allow for merging cells. There is too high a price to pay. Note that the value of a merged range is contained in its first cell. In your sheet A2:B2 is merged and the value appears in B2. But that's done by formatting. Excel finds the value in A2 and that prevents you from copying formulas efficiently. You find yourself forced to create a formula specially for column C which you can't copy to the right. It's the curse of the merged cell.
The alternative to merging cells is to use cell formatting. Delete column A, make column B wider and set the horizontal alignment Left with a large indent (perhaps 5 or 7). Everything will look the same but without the trouble. Note that the size of the indent depends on the total width of the column. An indent of 1 will be very small in a narrow column and uch bigger if the column is wide.
The exact arrangement of the worksheet will support automatic application of the code I'm providing today, and automation will support the workflow. You can't have an efficient whole if the parts don't match. We don't know yet how the final result will work but we do know that we don't want to be hampered by TEXT where dates should be or merged columns that will eat our time and the worksheet's efficiency, making it second-rate even before you can finish imagining it.