Summary Sheet

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)
 
R

Ron de Bruin

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
 
A

AD108

Thanks alot.

--
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top