Ignoring blank cells

[Ian1976]

Hi,
I’m using this macro and it works great, but it is giving me an error message if the ranges in m8:m47 on the status sheets 1/2 & 3 are blank?
Can anyone help me out to ignore blank cells in those 3 ranges?

Thanks

Sub NewButton_Click()
Dim fname As String

Application.ScreenUpdating = False

'fname = Range("J1") & Range("N1") & " " & Format(Range("L1"), "dd-mm-yyyy")
'ThisWorkbook.SaveAs ThisWorkbook.Path & "" & fname & ".xlsm", 52

Set cellrange1 = Range("M8:M47").Cells.SpecialCells(xlCellTypeConstants)
If Range("N1").Value = "1530" Then
Range("N1").Value = "0600"
Range("L1").Value = Range("L1").Value + 1
For Each cel In cellrange1
cel.Value = cel.Value + 1
Next
Else
Range("N1").Value = "1530"
End If

Sheets("Status Sheet 3502").Select
Set cellrange2 = Range("M8:M44").Cells.SpecialCells(xlCellTypeConstants)
If Range("N1").Value = "0600" Then
For Each cel1 In cellrange2
cel1.Value = cel1.Value + 1
Next
End If

 

[Fluff]

How about

On Error Resume Next
Set cellrange1 = Range("M8:M47").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not cellrange1 Is Nothing Then
   If Range("N1").Value = "1530" Then
      Range("N1").Value = "0600"
      Range("L1").Value = Range("L1").Value + 1
      For Each cel In cellrange1
         cel.Value = cel.Value + 1
      Next
   Else
      Range("N1").Value = "1530"
   End If
End If

Sheets("Status Sheet 3502").Select
On Error Resume Next
Set cellrange2 = Range("M8:M44").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not cellrange2 Is Nothing Then
   If Range("N1").Value = "0600" Then
      For Each cel1 In cellrange2
         cel1.Value = cel1.Value + 1
      Next
   End If
End If

 

[Joe4]

Maybe add some error handling code to specifically ignore just that specific error, and continue on. We can also denote that since there is no data found, to skip the For.Next loop, like that:

Sub NewButton_Click()

Dim fname As String
Dim dataFound As Boolean

Application.ScreenUpdating = False

'fname = Range("J1") & Range("N1") & " " & Format(Range("L1"), "dd-mm-yyyy")
'ThisWorkbook.SaveAs ThisWorkbook.Path & "" & fname & ".xlsm", 52

On Error GoTo err_chk
dataFound = True
Set cellrange1 = Range("M8:M47").Cells.SpecialCells(xlCellTypeConstants)
If Range("N1").Value = "1530" Then
    Range("N1").Value = "0600"
    Range("L1").Value = Range("L1").Value + 1
    If dataFound Then
        For Each cel In cellrange1
            cel.Value = cel.Value + 1
        Next
    End If
Else
    Range("N1").Value = "1530"
End If

dataFound = True
Sheets("Status Sheet 3502").Select
Set cellrange2 = Range("M8:M44").Cells.SpecialCells(xlCellTypeConstants)
If Range("N1").Value = "0600" Then
    If dataFound Then
        For Each cel1 In cellrange2
            cel1.Value = cel1.Value + 1
        Next
    End If
End If

On Error GoTo 0

Exit Sub

'   Error handling code
err_chk:
'   Ignore if no data found
    If Err.Number = 1004 Then
        dataFound = False
        Err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub

 

[Ian1976]

Thanks guys both options worked a treat but went with Fluffs! ??

 

[Fluff]

Glad we could help & thanks for the feedback