Thread
:
Summary Sheet
View Single Post
#
3
Posted to microsoft.public.excel.programming
AD108
external usenet poster
Posts: 72
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
Reply With Quote
AD108
View Public Profile
Find all posts by AD108