Help with a error

[bradyj7]

Hi there,

I have time stamps and acceleration data recorded from a trip in car. I'm analyzing the data every 10 seconds, so I have split the data up into 10 second intervals and compute a variety of parameters such as max, min average etc. However I also want to get the standard deviation of each 10 second segment for positive acceleration and negative acceleration(deceleration). I do this by filtering the rows to only display positive and negative values and then compute the standard deviation. It works fine when both acceleration and deceleration occur in a segment and the values computed are correct.

A problem arises though when the program tries to calculate the standard deviation of deceleration of a segment when only positive acceleration has occurred in the entire 10 second segment because when you filter the cells there a no cells left (because they were all positive). Then you get an error saying "There are no cells".

I put in the line "On Error resume next" to just continue with the program hoping that it would leave the box blank or give the #DIV/0! error.

However its not doing that and instead is outputting a random value which makes no sense. How could it produce a value if there are no cells to compute?

Would anybody know whats going on?

'Standard deviation Acceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:=">0"  'only display cases of acc
    End With

    StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    On Error Resume Next
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 16).Value = StandardDevAcc
    
    'Standard deviation Deccceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:="<0"  'only display cases of deacc
    End With

    
    StandardDevDeacc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    On Error Resume Next
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 17).Value = StandardDevDeacc

 

[Jonmo1]

Try something like this

StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
If Not Iserror(StandardDevAcc) Then
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 16).Value = StandardDevAcc
End If

Hope that helps.

 

[bradyj7]

Hi,

Thanks for the reply. Do you mean like this?

'Standard deviation Acceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:=">0"  'only display cases of acc
    End With

    StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    If Not IsError(StandardDevAcc) Then
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 16).Value = StandardDevAcc
    End If
    
    'Standard deviation Deccceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:="<0"  'only display cases of deacc
    End With

    
    StandardDevDeacc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    If Not IsError(StandardDevDeacc) Then
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 17).Value = StandardDevDeacc
    End If

With this code, for example when the cells have been filtered to display values >0 but all the cells in that segment were <0, there are no cells left and you get the "No cells were found" error.

Any ideas?