I am making a summary sheet to average values in a specific range in
multiple worksheets.

The loop itself is working, but the variable is returning as "0". Any help
would be appreciated.

Code is as follows


Option Explicit
Public dblAverage As Double

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Documents and Settings\Ariel Dugan\Desktop\TEST"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "Fill*.xls"

If .Execute 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults =
Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

dblAverage = dblAverage + Sheets(2).Range("C:5").Value
MsgBox Range("C:5").Value
wbResults.Close SaveChanges:=True

Next lCount
End If
End With
Call tstMessage

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Sub tstMessage()
MsgBox "The answer is" & dblAverage

End Sub



--
Ariel Dugan
Assistant Manager
Down To Earth Natural Foods
808-947-7678 Phone
808-943-8491 Fax
808-282-5916 Cell