Help edit this code so that it loops through specific worksheets

**rocksan** · 07-18-2012, 01:25 AM

Hi everyone, can anyone help edit this code so that it loops through specific named worksheets "A", "B", "C","D" in my workbook.

Thanks.

Sub ClearERRvalues()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:z1000")
For Each cell In rng
If cell.Text = "#DIV/0!" Then
cell.ClearContents
ElseIf cell.Text = "#N/A" Then
cell.ClearContents
End If
Next cell
End Sub

**Trebor76** · 07-18-2012, 01:37 AM

Hi rocksan,

Are the error messages the result of existing formulas or have they been converted to values so it's just the text that's showing?

Robert

**rocksan** · 07-18-2012, 01:54 AM

Hi Robert,

Originally Posted by Trebor76

Hi rocksan,

Are the error messages the result of existing formulas or have they been converted to values so it's just the text that's showing?

Robert

The formulas are used to do some calculation, sometimes the calculation divides by zero, thats why it results in the error. The N/A happens when the data itself is unavailable yet for calculation. Another sheets uses the calculated data, since the data is either #DIV/0 or #N/A, the whole formula fails. The macro is just a quick fix i need to clear those errors on corresponding sheets, I just want it to loop automatically, instead of finding and fixing the affected sheet manually.

**Trebor76** · 07-18-2012, 02:03 AM

OK so I'll assume the errors are the result of existing formula's, try this (initally on a copy of the workbook in case the results are not as expected):

Option Explicit
Sub Macro3()

    'http://www.excelforum.com/excel-programming/846061-help-edit-this-code-so-that-it-loops-through-specific-worksheets.html

    Dim varMySheet As Variant
    
    For Each varMySheet In Array("A", "B", "C", "D")
        With Sheets(varMySheet).Range("A1:Z1000")
            On Error Resume Next 'OK to ignore 'no cells found error message'
                .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents 'Clear existing formula generated errors
            On Error GoTo 0
        End With
    Next varMySheet
    
End Sub

HTH

Robert

**rocksan** · 07-18-2012, 03:21 AM

The code works perfectly.

You're so helpful. Thank you very much Robert.

**Trebor76** · 07-18-2012, 05:35 AM

Thanks for the feedback and I'm glad we were able to solve the issue

Help edit this code so that it loops through specific worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Help edit this code so that it loops through specific worksheets

Re: Help edit this code so that it loops through specific worksheets

Re: Help edit this code so that it loops through specific worksheets

Re: Help edit this code so that it loops through specific worksheets

Re: Help edit this code so that it loops through specific worksheets

Re: Help edit this code so that it loops through specific worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions