I think there was a problem in passing the range to your function - so this does the calculation in the routine and bypasses the function
Sub Night_LAeq_AvgXP(): Dim U As Range, F As Range, t As Long, c As Long
Dim S As Double, RT As Double, Y As Double, n As Long, dB_ As Double: n = 0
Dim StartTime As Date, EndTime As Date, D As Date, i As Long, R As Long
Set F = Columns.Find("LAeq")
If Not F Is Nothing Then c = F.column Else Exit Sub
Set F = Columns.Find("Time")
If Not F Is Nothing Then t = F.column Else Exit Sub
R = F.row + 1: D = Cells(R, t - 1)
StartTime = #11:00:00 PM#: EndTime = #7:00:00 AM#
For i = R To Columns(t - 2).Find("*", , , , xlByRows, xlPrevious).row
If IsNumeric(Cells(i, t)) Then
S = CDbl(Cells(i, t))
If S >= CDbl(StartTime) Or S < CDbl(EndTime) Then
Y = Cells(i, c): n = n + 1: RT = RT + 10 ^ (Y / 10)
If U Is Nothing Then _
Set U = Cells(i, c) Else Set U = Union(U, Cells(i, c))
End If: End If: Next i
U.Interior.ColorIndex = 6: dB_ = 10 * (Log(RT / n) / Log(10))
Cells(i + 11, c).Select
ActiveCell = Round(dB_, 1)
ActiveCell.Offset(0, -1).Select
ActiveCell = "23:00 - 07:00"
ActiveCell.Offset(0, -1).Select
ActiveCell = "Night 8hr"
End Sub
AND - Here's the day version:
Sub Day_LAeq_AvgXP(): Dim U As Range, F As Range, t As Long, c As Long
Dim S As Double, RT As Double, Y As Double, n As Long, dB_ As Double: n = 0
Dim StartTime As Date, EndTime As Date, D As Date, i As Long, R As Long
Set F = Columns.Find("LAeq")
If Not F Is Nothing Then c = F.column Else Exit Sub
Set F = Columns.Find("Time")
If Not F Is Nothing Then t = F.column Else Exit Sub
R = F.row + 1: D = Cells(R, t - 1)
StartTime = #7:00:00 AM#: EndTime = #11:00:00 PM#
For i = R To Columns(t - 1).Find("*", , , , xlByRows, xlPrevious).row
If IsNumeric(Cells(i, t)) Then
S = CDbl(Cells(i, t))
If S >= CDbl(StartTime) And S < CDbl(EndTime) Then
Y = Cells(i, c): n = n + 1: RT = RT + 10 ^ (Y / 10)
If U Is Nothing Then _
Set U = Cells(i, c) Else Set U = Union(U, Cells(i, c))
End If: End If: Next i
U.Interior.ColorIndex = 3: dB_ = 10 * (Log(RT / n) / Log(10))
Cells(i + 15, c).Select
ActiveCell = Round(dB_, 1)
ActiveCell.Offset(0, -1).Select
ActiveCell = "7:00 - 23:00"
ActiveCell.Offset(0, -1).Select
ActiveCell = "Day 16hr"
End Sub
Bookmarks