Summary Sheet
 

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

Summary Sheet
 

Hi AD108

dblAverage = dblAverage + Sheets(2).Range("C:5").Value

Change to
dblAverage = dblAverage + Sheets(2).Range("C5").Value

Also add wbResults to the code

dblAverage = dblAverage + wbResults.Sheets(2).Range("C5").Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AD108" wrote in message ...
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

Summary Sheet
 

Thanks alot.

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

"Ron de Bruin" wrote in message
...
Hi AD108

dblAverage = dblAverage + Sheets(2).Range("C:5").Value

Change to
dblAverage = dblAverage + Sheets(2).Range("C5").Value

Also add wbResults to the code

dblAverage = dblAverage + wbResults.Sheets(2).Range("C5").Value


--
Regards Ron de Bruin
http://www.rondebruin.nl


"AD108" wrote in message
...
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