CODEWITHSUNDEEP
excelvba
Jelle Ooms
Jelle Ooms

Reputation: 11

IFERROR in in this macro?

The problem is that when I change the value in I16 or I17 I get an error. How can I prevent this error from happening? I check in I16 and I17 for the sheetnames, because every week an updated sheet comes available. Thank you

Sub Compare()


Call compareSheets(range("I16").Value, range("I17").Value)


End Sub




Sub compareSheets(Sofon As String, Sofon2 As String)


Dim mycell As range
Dim mydiffs As Integer


For Each mycell In ActiveWorkbook.Worksheets(Sofon2).range("M:M")
If Not mycell.Value = ActiveWorkbook.Worksheets(Sofon).Cells(mycell.Row,  mycell.Column).Value Then


mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1


End If
Next


MsgBox mydiffs & " differences found in Column M (Salesman)", vbInformation


ActiveWorkbook.Sheets(Sofon2).Select


End Sub

Upvotes: 0

Views: 509

Answers (3)

user4691433
user4691433

Reputation:

You could use something similar to this to call compareSheets. It will warn you if either of the two ranges do not correspond to sheet names and won't call compareSheets if true.

Dim Sheet1 As Worksheet
Dim boolI16SheetCheck As Boolean
Dim boolI17SheetCheck As Boolean

    boolI16SheetCheck = False
    boolI17SheetCheck = False

    For Each Sheet1 in ActiveWorkbook.Worksheets
        If Sheet1.Name = Activesheet.Range("I16").Value Then boolI16SheetCheck = True
        If Sheet1.Name = Activesheet.Range("I17").Value Then boolI17SheetCheck = True
        If boolI16SheetCheck = True And boolI17SheetCheck = True Then
            Call compareSheets(range("I16").Value, range("I17").Value)
            Exit Sub
        End If
    Next Sheet1

    If boolI16SheetCheck = False Then
        If boolI17SheetCheck = False Then
            Msgbox "Neither I16 nor I17 sheet found."
        Else
            Msgbox "I16 sheet not found."
        End If
    Else
        Msgbox "I17 sheet not found."
    End If

End Sub

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27259

Since the OP wanted an ISERROR type of solution, I decided to post the code which incorporates a function to check if a sheet exists in a workbook. The concept is similar to answers already posted, but it keeps any On Error statements strictly inside the function and uses regular code blocks to evaluate errors.

Sub Compare()

Dim bGo As Boolean
Dim s1 As String, s2 As String
s1 = Range("I16").Value2
s2 = Range("I17").Value2

If Not WorksheetExist(s1) Then
    bGo = False
    MsgBox "The sheet " & s1 & " does not exist in this workbook."
End If

If Not WorksheetExist(s2) Then
    bGo = False
    MsgBox "The sheet " & s2 & " does not exist in this workbook."
End If

If bGo Then compareSheets s1, s2

End Sub


Function WorksheetExist(sName As String, Optional wb As Workbook) As Boolean

     Dim wbCheck As Workbook, ws As Worksheet
     If wb Is Nothing Then Set wbCheck = ThisWorkbook Else: Set wbCheck = wb

     On Error Resume Next
     Set ws = wbCheck.Sheets(sName)
     On Error GoTo 0

     If Not ws Is Nothing Then WorksheetExist = True Else: WorksheetExist = False

End Function

And, based on @puzzlepiece87 methodology, here is an improved WorksheetExist Function that eliminates of On Error statements altogether.

Function WorksheetExist(sName As String, Optional wb As Workbook) As Boolean

Dim wbCheck As Workbook, ws As Worksheet
If wb Is Nothing Then Set wbCheck = ThisWorkbook Else: Set wbCheck = wb

WorksheetExist = False

For Each ws In wbCheck.Worksheets
    If ws.Name = sName Then
        WorksheetExist = True
        Exit For
    End If
Next

End Function

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152585

Just to show what I was thinking.

I agree with puzzlepiece87 that On Error is finicky, but with something this simple I would use it to avoid the excess loops.

Sub compareSheets(Sofon As String, Sofon2 As String)

Dim mycell As Range
Dim mydiffs As Integer

On Error GoTo nosheet
For Each mycell In ActiveWorkbook.Worksheets(Sofon2).Range("M:M")
    If Not mycell.Value = ActiveWorkbook.Worksheets(Sofon).Cells(mycell.Row, mycell.Column).Value Then
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
    End If
Next


MsgBox mydiffs & " differences found in Column M (Salesman)", vbInformation
ActiveWorkbook.Sheets(Sofon2).Select
Exit Sub

nosheet:
If Err.Number = 9 Then
    MsgBox "One or both sheets do not exist"
Else
    MsgBox Err.Description
End If

End Sub

Upvotes: 2

Related Questions