Type Mismatch

[SBF12345]

I am receiving a type mismatch on the line below and am not sure how to make the correction.

If .Cells(E, 30 + D).value = "#DIV/0!" Then

The target cell at the moment of the error has "#DIV/0!" as its contents.

The line exists within a with statement:

With wkbk.Sheets("Ratios")    
        If IsError(.Cells(E, 30 + D).value) Then
        .Rows(E).Delete
        End If

        If .Cells(E, 30 + D).value = "#DIV/0!" Then
        .Rows(E).Delete
        End If


        If .Cells(E, 30 + D).value = "" Then
        .Rows(E).Delete
        End If
    
        End With

All of the variables are declared as integers. Are there other sources of potential mismatched data types?

 

[Fluff]

You're first If statement should delete any rows with Div0 error, so you shouldn't get the typemismatch error.

 

[SBF12345]

I reduced the section of code to what I have below:

When I run the code it doesn't appear as though the nested loops are running over all the cells that I would like them to. This is based on the clock speed of the time it takes from the beginning of this section to the point I run into a subsequent error AND the fact that the error is caused by a cell that still includes a cell value of "#DIV/0!". I'm not sure why the lines below would just be skipped over during runtime. Any ideas?

For D = 31 To B    
        For E = 4 To C
        
        If IsError(wkbk.Sheets("Ratios").Cells(E, 30 + D).value) Then
            wkbk.Sheets("Ratios").Rows(E).Delete
        End If
        
        Next E
        
    Next D

 

[Fluff]

When deleting rows you need to loop backwards (ie bottom up)

 

[pgc01]

Hi

Are the error values the result of formulas or are they constant values?

 

[SBF12345]

I changed the rows part of the loop to step backwards, but I am still receiving the error later in the code. The errors are R1C1formula generated.

I suspect the error is caused by the formula and the way the input data is presented. I am going to copy pastevalues the entire area I would like then do the nested loop to get rid of error values.

 

[Fluff]

With the formulae in those columns try

   Range(Cells(1, 61), Cells(1, 30 + b)).EntireColumn.SpecialCells(xlFormulas, xlErrors).EntireRow.Delete

 

[SBF12345]

I changed the order of operations in the script so that the cell filter runs after the cell values are pasted to a new sheet thus eliminating the formula generated error. Thanks for your help!

 

[Fluff]

Glad it's sorted & thanks for the eedback