Sorting and deleting rows. Run-time error '13': Type mismatch

user_19407090 0
Viewed 44

I tried to work with macros that should

  1. sort p-values in column H
  2. delete all the rows with p-values >=0.05
  3. fill column I with "B - E", I mean I2=B2-E2; I3=B3-E3 et cetera
  4. sort by column I

This macros was created by Jeeped:

Sub sort1()
    Dim m As Variant

    With ActiveSheet
        'delete rows 1:6
        .Range("1:6").EntireRow.Delete Shift:=xlUp

        'new column header for column I
        Range("I1") = "diff"

        'sort A:I on column H (ascending)
        With .Range("A:I")
            .Sort Key1:=.Columns(8), Order1:=xlAscending, Header:=xlYes
        End With

        'find >=0.05
        m = Application.Match(0.05, .Range("H:H"), 0)
        If IsError(m) Then m = Application.Match(0.05, .Range("H:H"))

        'delete rows (>=0.05):<bottom of worksheet>
        .Range(.Cells(m, "A"), .Cells(.rows.Count, "A")).EntireRow.Delete Shift:=xlUp

        'new formula for column I data range
        .Range("I2:I" & m - 1).FormulaR1C1 = "=RC[-7]-RC[-4]"

        'calculate (actually unnecessary, putting in new formulas forces true calculation)
        .Calculate

        'sort A:I on column I (ascending)
        With .Range("A:I")
            .Sort Key1:=.Columns(9), Order1:=xlAscending, Header:=xlYes  
        End With
    End With
End Sub

It worked before but now it stops on the string

.Range(.Cells(m, "A"), .Cells(.rows.Count, "A")).EntireRow.Delete Shift:=xlUp

It says

Run-time error '13': Type mismatch

Please help!

rowdeleting vba excel sorting
1 Answers

Without knowing the data that this sub is processing it is difficult to guess why are you getting that error. However, my bet is that the Application.Match function can't find any values in column H that are less than or equal to 0.05, so m = #N/A and you can't pass #N/A to the .Cells() property.

If that is the case (you can check if it is in the immediate window after the error appears), try deleting the rows and sorting only if MATCH was successful:

Sub sort1()
'begin your code here

'find >=0.05
m = Application.Match(0.05, .Range("H:H"), 0)
If IsError(m) Then m = Application.Match(0.05, .Range("H:H"))

If Not IsError(m) Then
    'delete rows (>=0.05):<bottom of worksheet>
    .Range(.Cells(m, "A"), .Cells(.rows.Count, "A")).EntireRow.Delete Shift:=xlUp

    'new formula for column I data range
    .Range("I2:I" & m - 1).FormulaR1C1 = "=RC[-7]-RC[-4]"

    'calculate (actually unnecessary, putting in new formulas forces true calculation)
    .Calculate
End If

'sort A:I on column I (ascending)
With .Range("A:I")
    .Sort Key1:=.Columns(9), Order1:=xlAscending, Header:=xlYes
End With

'Continue your code here
End Sub

P.S, your code currently doesn't do exactly what you said all the times:

You wanted to delete all rows that are bigger than or equal to 0.05, but if you didn't have a value that is exactly equal to 0.05, and had values like:(0, ..., 0.048, 0.049, 0.051, 0.052, ...) in your H column, then this line of code:

If IsError(m) Then m = Application.Match(0.05, .Range("H:H"))

will find the largest value that is less than or equal to 0.05, which is 0.049 in our hypothetical data.
Then this line of code:

.Range(.Cells(m, "A"), .Cells(.rows.Count, "A")).EntireRow.Delete Shift:=xlUp

will delete the rows which had the values from 0.049 to the end (0.049 included).

You code is deleting one extra value more than what you wanted, but since you said it worked before as you wanted, I didn't change it.

If you want to correct this behavior, you need to add one to m the second time you use MATCH:

If IsError(m) Then m = Application.Match(0.05, .Range("H:H")) + 1
user_95651060
Related