Blank cell gives error 13 (Mismatch) in loop - edit code to accommodate for blanks

[onmyway]

Hi guys,

I hope you can help me.

I have a procedure that loops through a range of cells, updating my spreadsheet with the required data. But when one of my cells is blank in my range, I get an error. How can I edit my code to not see my blank cell as an error? Just moves on?

Note: I have underlined the part in the code where the loop starts.

Private Sub nEngineeringHours()
'UserForm_Initialize

Dim Sws As Worksheet, Dws As Worksheet
Dim Slr As Long, Dlr As Long
Dim Srng As Range, Scell As Range, Drng As Range, Dcell As Range
Dim sDate, eDate, location, delaycode, operator, delaytime, Equipment

   On Error GoTo nEngineeringHours_Error

If Sheets("DataBaseDelay").AutoFilterMode Then ActiveSheet.AutoFilterMode = False

'Sws is the source sheet where the master data is placed.
Set Sws = Sheets("DataBaseDelay")

'Dws is the destination sheet where report will be generated
Set Dws = Sheets("Dashboard")

'Slr is the last row used in col. B of source sheet
Slr = Sws.Cells(Rows.count, "EQ").End(xlUp).row

'Dlr is the last row used in col. A of destination sheet
Dlr = Dws.Cells(Rows.count, "A").End(xlUp).row

'Setting the ranges
Set Srng = Sws.Range("EQ4:EQ" & Slr)
Set Drng = Dws.Range("A30:A41")

'Assigning the form controls to the variable, will be easy to reference in the code
sDate = txtStartDate
eDate = txtEndDate
location = cboLocation
delaycode = cboDelayCode
operator = cboOperator
Equipment = cbxEquipment

Dws.Range("C1") = "From " & Me.txtStartDate & " to " & Me.txtEndDate
Dws.Range("C2") = cboLocation
Dws.Range("C3") = cboOperator
Dws.Range("C4") = cbxEquipment

cnt = 0
delaytime = 0

'Looping through the cells in Drng which is haveing delay codes in col. A on Report Sheet
For Each Dcell In Drng
        'if All is selected as delay code in combobox , proceed otherwise goto else
        If delaycode = "All" Then
                
                'Assuming All is selected from the combobox for delaycode, then looping through all the cells on Source Sheet
                For Each Scell In Srng
                        'If All is selected as location, proceed otherwise goto else
                        If location = "All" Then
                                'If All is selected as location and All is selected as operator, proceed otherwise goto else
                                If operator = "All" Then
                                        'checking if all the above conditions are met and the code on source sheet is equal to the code in the report sheet within the loop
                                        If Dcell = Scell.Offset(0, 2) Then
                                                'adding delay time in col. G and counting the each occurrence of cell which met the above criteria, also checking the Date criteria
                                                If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                                End If
                                        End If
                                Else
                                        
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And Dcell = Scell.Offset(0, 2) And Scell.Offset(0, -23) = operator And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                        
                                End If
                        Else
                                'If All is not selected as location and All is selected as operator, proceed otherwise goto else
                                If operator = "All" Then
                                        'checking if all the above conditions are met and the code on source sheet is equal to the code in the report sheet within the loop and location is same as selected in combobox
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And Dcell = Scell.Offset(0, 2) And Scell.Offset(0, 3) = location And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                'adding delay time in col. G and counting the each occurrence of cell which met the above criteria
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                Else
                                        'checking if location and operator both matches with the combobox selection and code on both the sheets matches within the loop
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And Dcell = Scell.Offset(0, 2) And Scell.Offset(0, 3) = location And Scell.Offset(0, -23) = operator And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                        
                                End If
                                
                                
                        End If
                        
                Next Scell
        
        Else
                'Now if All is not selected in the delay code combobox the following code will tke the control
                'Assuming All is selected from the combobox for delaycode, then looping through all the cells on Source Sheet
                For Each Scell In Srng
                        'If All is selected as location, proceed otherwise goto else
                        If location = "All" Then
                                'If All is selected as location and All is selected as operator, proceed otherwise goto else
                                If operator = "All" Then
                                        'checking if all the above conditions are met and the code on source sheet is equal to the code in the report sheet within the loop
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And delaycode = Scell.Offset(0, 2) And Dcell = delaycode And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                'adding delay time in col. G and counting the each occurrence of cell which met the above criteria
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                Else
                                        
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And delaycode = Scell.Offset(0, 2) And Dcell = delaycode And Scell.Offset(0, -23) = operator And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                        
                                End If
                        Else
                                'If All is not selected as location and All is selected as operator, proceed otherwise goto else
                                If operator = "All" Then
                                        'checking if all the above conditions are met and the code on source sheet is equal to the code in the report sheet within the loop and location is same as selected in combobox
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And delaycode = Scell.Offset(0, 2) And Dcell = delaycode And Scell.Offset(0, 3) = location And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                'adding delay time in col. G and counting the each occurrence of cell which met the above criteria
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                Else
                                        'checking if location and operator both matches with the combobox selection and code on both the sheets matches within the loop
                                        If DateValue(Scell) >= DateValue(sDate) And DateValue(Scell) <= DateValue(eDate) And delaycode = Scell.Offset(0, 2) And Dcell = delaycode And Scell.Offset(0, 3) = location And Scell.Offset(0, -23) = operator And Scell.Offset(0, 4) = "Non-Engineering" Then
                                                delaytime = delaytime + Scell.Offset(0, 6)
                                                nDelayTotal = nDelayTotal + Scell.Offset(0, 6)
                                                cnt = cnt + 1
                                        End If
                                        
                                End If
                                
                                
                        End If
                        
                Next Scell

        End If
        
        'Placing the cnt and delaytime in col. B and col. C on report sheet
        Dcell.Offset(0, 1) = cnt
        Dcell.Offset(0, 2) = delaytime
        Dcell.Offset(0, 2).NumberFormat = "[h] "" hours & "" m "" Minutes"""
'        Dcell.Offset(0, 6) = delaytime
'        Dcell.Offset(0, 6).NumberFormat = "[h]"":""mm"
        Dcell.Offset(0, 4) = delaytime * 24
        Dcell.Offset(0, 4).NumberFormat = "#,##0.00"
        
        
        cnt = 0
        delaytime = 0
        
Next Dcell

        Dws.Range("B24") = nDelayTotal
        Dws.Range("B24").NumberFormat = "[h]"":""mm"

   On Error GoTo 0
   Exit Sub

nEngineeringHours_Error:

    msgbox "Error " & Err.Number & " (" & Err.Description & ") in procedure nEngineeringHours of Form Reports"
        
End Sub

Thank you in advance!

[JOHN H. DAVIS]

Maybe:

Add an If statement at that location.

If cell.value <> "" then do something

[Norie]

Where do you actually get the error?

[onmyway]

Where do you actually get the error?

Hi Norie,

I believe the error occurs when the loop compares my destination cell with my source cells, and where this destination cell is blank. The loop moves down in my range of destination cells with the "next Dcell" method, and when one of these Dcell are empty, I get the error.

Hope it makes sense!

[onmyway]

Hi John,

Thank you for the responce.

I have tried it like this:

For Each Dcell In Drng

    If Dcell.Value <> "" Then

carry on with the code

But get the same error.

i have also tried it as:

If Dcell.value = "" or 0 Then

*nothing

else

carry on with the code

Also the same error....

Am I putting it in the right place?

[Norie]

I don't see how that would cause a type mismatch.

Can you identify which line of code the error actually occurs on?

[onmyway]

I have found my mistake.....

I had my Zero (0) values formatted to show as blanks in my custom format: 0;-0;;@

Therefore this did not work:

If Dcell.value = "" then

'Run code

Else

Goto 1000

End if

Thank you all!