Quick question

[bradyj7]

Quick question,

Would the code below replace all #Div/0! errors with blanks in a worksheet?

Thank you

Sub DeleteErrors()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub

 

[wigi]

Yes, but you can try this yourself - no?

It will clear all cells containing formulas that result in an error, not only because of dividing by 0.

 

[bradyj7]

Hi there,

That is what I though it would do, but I cannot get it to work.

I'm calculating the average acceleration of a vehicle every 10 seconds as it travels along. However sometimes no acceleration occurs in that 10 second time period and hence the #DIV/0! error is returned to the cell.

I am inserting the average values into a new sheet so there are no formulas in the cells only values or #Div/0!. How can make it at the end replace all #Div/0! with blanks?

Thank you

Aavg = Application.Subtotal(1, Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)))
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 9).Value = Aavg

 

[Jonmo1]

Here's the trick...

It DOES indeed work, it just doesn't do what you think it does.

It replaces FORMULAS that result in error with blank..
Your cells don't contain formulas, they contain the result of your code that resulted in an error.

Instead of
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).ClearContents

Try
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 16).ClearContents

Hope that helps.

 

[Jonmo1]

Or even better, don't put the error in the cells in the first place...

Try this

Aavg = Application.Subtotal(1, Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)))
    Worksheets("kinematic").Activate
    If IsError(Aavg) Then
        'Do nothing
    Else
        ActiveCell.Offset(0, 9).Value = Aavg
    End If

 

[bradyj7]

hey thanks, that was bugging me, both solutions work great

 

[Jonmo1]

Glad to help, thanks for the feedback.