VBA error on loop when no data to average

[amphead]

Hello,

One of my coworkers created a VBA to average some data years ago. He has since retired and I need help correcting an error. I'm new to VBA.

I have a data point for every hour of the month. The VBA code is intended to calculate a 24 hour average. I've ran into an error due to an entire day of missing hour data, so no data to average for that day. The VBA gives me Run-time error "6": Overflow.

Can anyone provide me some direction getting the VBA code to move on to the next day if there is no data to calculate for the 24 hour average?

' calculate 24 hour averages
For iday = 1 To itotdays
  istrt = (iday - 1) * 24 + 2
  istop = istrt + 23
  asum = 0
  n = 0
  For i = istrt To istop
    arange$ = "B" & i
    Set arang = Range(arange$)
    icnt = Application.WorksheetFunction.Count(arang)
    If icnt = 1 Then
      asum = asum + Cells(i, 2)
      n = n + 1
      asumtot = asumtot + Cells(i, 2)
      ntot = ntot + 1
    End If
    
  Next i
  aver = asum / n
  If n > 17 Then
    Cells(istop, 4) = aver
  End If
Next iday

When I click on debug, it highlights this line.

aver = asum / n

 

[Joe4]

Welcome to the Board!

Hard to see/test without having your data, by maybe try this?

For iday = 1 To itotdays
  istrt = (iday - 1) * 24 + 2
  istop = istrt + 23
  asum = 0
  n = 0
  For i = istrt To istop
    arange$ = "B" & i
    Set arang = Range(arange$)
    icnt = Application.WorksheetFunction.Count(arang)
    If icnt = 1 Then
      asum = asum + Cells(i, 2)
      n = n + 1
      asumtot = asumtot + Cells(i, 2)
      ntot = ntot + 1
    End If  
  Next i

  If n > 0 Then
    aver = asum / n
    If n > 17 Then
        Cells(istop, 4) = aver
    End If
  End If

Next iday

 

[amphead]

Thanks for your help. I just added the extra If...Then and that corrected my problem.

Sorry about not adding data to the post. I'll try to remember to do that on the next one.

 

[Joe4]

You are welcome.
Glad my solution worked for you!