On error resume next question

[ipbr21054]

Hi,
I have the code below in use

All works fine but if i run the code & there are no vales in the cells to be cleared i get a run time error 1004 NO CELLS FOUND

Private Sub ClearSheet_Click()
Dim answer As Integer
 answer = MsgBox("IS IT OK TO DELETE SHEET FIGURES ? ", vbCritical + vbYesNo + vbDefaultButton2, "DLETE WORKSHEET FIGURES MESSAGE")
    If answer = vbNo Then
      Exit Sub
      End If
Range("B1:D1").ClearContents
Range("A34").ClearContents
Sheet12.Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
Range("A3").Select
MsgBox "CELLS HAVE NOW BEEN CLEARED", vbInformation

End Sub

If i put On error resume next after the Private Sub part,

1, Will that stop the error message ?
2, Do i need to add anything else or is On error resume next enough ?

Thanks please advise.

 

[Peter_SSs]

If i put On error resume next after the Private Sub part,

1, Will that stop the error message ?

Yes, but I would not do that as it could also hide other errors that you might need to know about.

Just add it where needed and turn it off as soon as possible.

Private Sub ClearSheet_Click()
Dim answer As Integer
 answer = MsgBox("IS IT OK TO DELETE SHEET FIGURES ? ", vbCritical + vbYesNo + vbDefaultButton2, "DLETE WORKSHEET FIGURES MESSAGE")
    If answer = vbNo Then
      Exit Sub
      End If
Range("B1:D1").ClearContents
Range("A34").ClearContents
On Error Resume Next
Sheet12.Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Range("A3").Select
MsgBox "CELLS HAVE NOW BEEN CLEARED", vbInformation

End Sub

 

[ipbr21054]

Morning,
I have done what you advised thanks & it still has an issue on one worksheet.

I applied the code to the existing code thus below now in use.

Private Sub ClearSheet_Click()
Dim answer As Integer
 answer = MsgBox("IS IT OK TO DELETE SHEET FIGURES ? ", vbCritical + vbYesNo + vbDefaultButton2, "DLETE WORKSHEET FIGURES MESSAGE")
    If answer = vbNo Then
      Exit Sub
      End If
Range("B1:D1").ClearContents
Range("A34").ClearContents
On Error Resume Next
Sheet12.Range("A3:D29").SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Range("A3").Select
MsgBox "CELLS HAVE NOW BEEN CLEARED", vbInformation

End Sub

After i click OK to the message CELLS HAVE NOW BEEN CLEARED i see a run time error message 13 Mismatch
When i debug the following code is shown in yellow.

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("D3:D29")) Is Nothing Then

On Error Resume Next
If UCase(Target.Value) = "L" Then Target.Value = "4"
If UCase(Target.Value) = "W" Then Target.Value = "5"
If UCase(Target.Value) = "B" Then Target.Value = "6"
If UCase(Target.Value) = "C" Then Target.Value = "7"
On Error GoTo 0

End If
    If Not (Application.Intersect(Target, Range("A2:D29")) _
      Is Nothing) Then
        With Target
            If Not .HasFormula Then
                Application.EnableEvents = False
                .Value = UCase(.Value)
                Application.EnableEvents = True
            End If
        End With
    End If

End Sub

When using the worksheet in column D range i would enter L, W, B or C to the apply the value to the cell 4,5,6 & 7

This part runs as its a change event so how can i stop it showing the error message.

I did try the same approach as above which didnt show the message but then just put the numerical value in cells A,B,C & D
Thanks