VBA Script to Average Amplitude Values in Each Bin Range of a Frequency Histogram

[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

[jnmonroe]

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.

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

[p45cal]

change:

avg = avg / N

to:

If N > 0 Then avg = avg / N

?
It's balking at a division by 0.

later...

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.

OK.. change:

avg = avg / N
Cells(c + 3, 8) = avg

to one line

  If N > 0 Then Cells(c + 3, 8) = avg / N Else Cells(c + 3, 8) = Empty

[jnmonroe]

change:

avg = avg / N

to:

If N > 0 Then avg = avg / N

?
It's balking at a division by 0.

later...
OK.. change:

avg = avg / N
Cells(c + 3, 8) = avg

to one line

  If N > 0 Then Cells(c + 3, 8) = avg / N Else Cells(c + 3, 8) = Empty

[p45cal]

I'm p45cal, not mark007