A
AD108
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
(e-mail address removed)
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
(e-mail address removed)