Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a summary sheet | New Users to Excel | |||
Summary Sheet | Excel Discussion (Misc queries) | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Summary Sheet | Excel Worksheet Functions | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) |