1 Attachment(s)
VBA Script to Average Amplitude Values in Each Bin Range of a Frequency Histogram
When running the VBA script below, the script throws a Run-time error and stops as soon as it hits a sound frequency (Freq.) bin which is empty. The script should output an average amplitude (Ave.Amp.) of zero if the bin does not contain any frequency values.
Can anyone suggest something which might allow the script to run without the run-time error and place a zero in the Ave.Amp. column if there are no frequency values within that particular bin range?
Thank you.
Sub Histogram()
Dim c As Integer, i As Integer, j As Integer
Dim avg As Double
Dim binlow As Integer, binhigh As Integer
Dim N As Integer
For c = 1 To 16
binlow = Cells(c + 2, 2)
binhigh = Cells(c + 3, 2)
avg = 0
N = 0
For i = 1 To 11
If Cells(i + 2, 6) >= binlow And Cells(i + 2, 6) < binhigh Then
N = N + 1
avg = avg + Cells(i + 2, 7)
End If
Next i
avg = avg / N
Cells(c + 3, 8) = avg
Next c
End Sub
A clarification for my post titled "VBA Script to Average Amplitude Values in Each.."
Rather than outputting a zero when the bin range does not contain a frequency value, it's probably more appropriate for the VBA to output a blank.
Quote:
Originally Posted by
jnmonroe
When running the VBA script below, the script throws a Run-time error and stops as soon as it hits a sound frequency (Freq.) bin which is empty. The script should output an average amplitude (Ave.Amp.) of zero if the bin does not contain any frequency values.
Can anyone suggest something which might allow the script to run without the run-time error and place a zero in the Ave.Amp. column if there are no frequency values within that particular bin range?
Thank you.
Sub Histogram()
Dim c As Integer, i As Integer, j As Integer
Dim avg As Double
Dim binlow As Integer, binhigh As Integer
Dim N As Integer
For c = 1 To 16
binlow = Cells(c + 2, 2)
binhigh = Cells(c + 3, 2)
avg = 0
N = 0
For i = 1 To 11
If Cells(i + 2, 6) >= binlow And Cells(i + 2, 6) < binhigh Then
N = N + 1
avg = avg + Cells(i + 2, 7)
End If
Next i
avg = avg / N
Cells(c + 3, 8) = avg
Next c
End Sub
Mark007, Really appreciate the help. Works fine now.
Quote:
Originally Posted by
p45cal
change:
to:
Code:
If N > 0 Then avg = avg / N
?
It's balking at a division by 0.
later...
OK.. change:
Code:
avg = avg / N
Cells(c + 3, 8) = avg
to one line
Code:
If N > 0 Then Cells(c + 3, 8) = avg / N Else Cells(c + 3, 8) = Empty
