extending a function across multiple worksheets

[bobbybaggio]

I built this Macro to search through column B for a specific value (Whitemail in this case) and for every instance take the value from column F in the same row. The values in column F are time and the end result is the average. The Macro stops when it hits an empty cell. This works fine but I'm stuck on how to have the same function iterate through worksheet 1,2,3,4,n. At present I have to manually enter the worksheet to search. I tried using a loop but it fails validation everytime. What could I try now using this code below so that it returns the average time across the entire spreadsheet rather than just one worksheet??? Thanks for any advice

Sub Time_Average()

Dim i, LastRow, rng As Range, timeAvg
Dim v As Integer

LastRow = Range("'1'!C2").End(xlDown).Row
Set rng = Range("'1'!C2:C" & LastRow)
caseVal = "Whitemail"

If caseVal = "" Then
Exit Sub
End If

timeAvg = Application.SumIf(rng, caseVal, Range("'1'!F2:F" & LastRow)) _
/ Application.CountIf(rng, "=" & caseVal)
Range("'1'!D21").Value = timeAvg

End Sub

[rylo]

Hi

How about

Sub aaa()
  caseVal = "Whitemail"
  timesum = 0
  cntr = 0
  For Each sh In Sheets
    timesum = timesum + Application.SumIf(sh.Range("C:C"), caseVal, sh.Range("F:F"))
    cntr = cntr + Application.CountIf(sh.Range("C:C"), "=" & caseVal)
  Next sh
  MsgBox timesum & ", " & cntr & ", " & timesum / cntr
End Sub

rylo