Hi

Try this:

---Cut
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
If InStr(1, FilesInPath, CMonth & " " & CYear,
vbTextCompare) Then
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
End If
Loop
---Cut---


Hopes this helps.
....
Per

On 4 Aug., 23:26, Bishop wrote:
I have the following code:

Option Explicit

Sub Consolidate()
* * Dim MyPath As String
* * Dim FilesInPath As String
* * Dim MyFiles() As String
* * Dim Fnum As Long
* * Dim mybook As Workbook
* * Dim CalcMode As Long
* * Dim sh As Worksheet
* * Dim ErrorYes As Boolean
* * Dim DCLastRow As Integer 'DirectorCopy
* * Dim MCLastRow As Integer 'Monthly Compiler
* * Dim CMonth As String 'Compile Month
* * Dim CYear As String 'Compile Year
* * Dim Month As Integer

* * Dim center(18) As String
* * center(1) = "Bardstown"
* * center(2) = "Bothell"
* * center(3) = "VCollinsville"
* * center(4) = "El Paso"
* * center(5) = "Evansville"
* * center(6) = "Greensboro"
* * center(7) = "VHeathrow"
* * center(8) = "Joplin"
* * center(9) = "Kennesaw"
* * center(10) = "Lafayette"
* * center(11) = "Malvern"
* * center(12) = "VManhattan"
* * center(13) = "VMansfield"
* * center(14) = "VOttawa"
* * center(15) = "VPonco City"
* * center(16) = "VReno"
* * center(17) = "VSioux City"
* * center(18) = "VTerra Haute"

* * Dim FileCount As Long
* * Dim ScoringAve As Double
* * Dim i As Long

' * *If Cells(13, 4).Value = "January" Then Month = 1
' * *If Cells(13, 4).Value = "February" Then Month = 2
' * *If Cells(13, 4).Value = "March" Then Month = 3
' * *If Cells(13, 4).Value = "April" Then Month = 4
' * *If Cells(13, 4).Value = "May" Then Month = 5
' * *If Cells(13, 4).Value = "June" Then Month = 6
' * *If Cells(13, 4).Value = "July" Then Month = 7
' * *If Cells(13, 4).Value = "August" Then Month = 8
' * *If Cells(13, 4).Value = "September" Then Month = 9
' * *If Cells(13, 4).Value = "October" Then Month = 10
' * *If Cells(13, 4).Value = "November" Then Month = 11
' * *If Cells(13, 4).Value = "December" Then Month = 12
' * *CMonth = MonthName(Month, True)
* * 'This one line of code replaces the above 13 lines
* * CMonth = Left(Cells(13, 4).Value, 3)
* * CYear = Right(Cells(13, 7).Value, 2)

* * 'Fill in the path\folder where the files are
* * MyPath = "X:\C&A Analysts Team\PF Process\1 Tally & PF's Work in
Progress\Centers\"

* * For i = 1 To 18

' * * * *'Add a slash at the end if the user forget it
' * * * *If Right(MyPath, 1) < "\" Then
' * * * * * *MyPath = MyPath & "\"
' * * * *End If

* * * * 'If there are no Excel files in the folder exit the sub
* * * * FilesInPath = Dir(MyPath & center(i) & "\*" & CMonth & CYear &
"*.xl*")

* * * * If FilesInPath = "" Then
* * * * * * MsgBox "No files found in " & center(i)
* * * * * * GoTo ContinueLoop
* * * * End If

* * * * If FilesInPath < "" Then
* * * * * * FileCount = FileCount + 1
* * * * End If

* * * * 'Fill the array(myFiles)with the list of Excel files in the folder
* * * * Fnum = 0
* * * * Do While FilesInPath < ""
* * * * * * Fnum = Fnum + 1
* * * * * * ReDim Preserve MyFiles(1 To Fnum)
* * * * * * MyFiles(Fnum) = FilesInPath
* * * * * * FilesInPath = Dir()
* * * * Loop

All the files in these folders will be in the same format:

Center C&A PF Month Year Week Initials

So here's a sample of some files I'm working with:

VCollinsville C&A PF May 09 wk1 lb.xlsm
El Paso C&A PF Jun 09 wk3 gh.xlsm
Bardstown C&A PF Jul wk2 bm.xlsm

Here is a sample of the files found in one particular folder:

Bothell C&A PF May 09 wk4 gh
Bothell C&A PF Jul 09 wk2 gh
Bothell C&A PF Jul 09 wk5 gh
Bothell C&A PF Jun 09 wk3 gh

With this line of code:

FilesInPath = Dir(MyPath & center(i) & "\*" & CMonth & CYear & "*.xl*")

I'm attempting to isolate files that have a specific Month and Year. *So say
I only want July 09 files. *From the list above I would only be pulling:

Bothell C&A PF Jul 09 wk2 gh
Bothell C&A PF Jul 09 wk5 gh

But FilesInPath sees the first file, which means it's not "" and proceeds to
put all 4 files in the array. *How can I isolate only the files that qualify
under the Month/Year criteria and put only those files in the array?