CODEWITHSUNDEEP
vbaexcel
VBA Pete
VBA Pete

Reputation: 2666

Runtime error on delete loop

Any ideas why I am getting run-time error type mismatch for the code below? There seems to be an issue with the if statement.

The run-time error is type mismatch

Dim rng As Range
Dim cell_search As Range
Dim del As Range
Dim FrRngCount As Range
Dim I As Integer
Dim DatatoData As Worksheet

Set DatatoData = ThisWorkbook.Worksheets("PCA DATA2")

Set FrRngCount = DatatoData.Range("A:A")
I = Application.WorksheetFunction.CountA(FrRngCount)

Set rng = Intersect(DatatoData.Range("CV2:CV" & I), DatatoData.UsedRange)

For Each cell_search In rng
If (cell_search.Value) = "Not in Range" Then
    If del Is Nothing Then
        Set del = cell_search
    Else: Set del = Union(del, cell_search)
    End If
End If
Next cell_search
On Error Resume Next
del.EntireRow.Delete

Thanks for your help. Pete

Upvotes: 1

Views: 63

Answers (2)

zack.lore
zack.lore

Reputation: 527

Is rng being set to Nothing? Try this:

Option Explicit

Sub Test()

Dim rng As Range
Dim cell_search As Range
Dim del As Range
Dim FrRngCount As Range
Dim I As Integer
Dim DatatoData As Worksheet

Set DatatoData = ThisWorkbook.Worksheets("PCA DATA2")

Set FrRngCount = DatatoData.Range("A:A")
I = Application.WorksheetFunction.CountA(FrRngCount)    

Set rng = Intersect(DatatoData.Range("CV2:CV" & I), DatatoData.UsedRange)

If Not rng Is Nothing Then

For Each cell_search In rng
    cell_search.Activate
    If cell_search.Value = "Not in Range" Then
        If del Is Nothing Then
            Set del = cell_search
        Else: Set del = Union(del, cell_search)
        End If
    End If
Next cell_search

End If

On Error Resume Next
del.EntireRow.Delete

End Sub

This is what I have and it seems to work. I added the cell_search.Active for debugging. You should give this a try and see what cell it activates when you get your error.

Upvotes: 1

Ralph
Ralph

Reputation: 9444

Based on your above comment (where the error occurs) it seems that the .Value of that cell cannot be determined. This is the case, when a formula is in that cell which produces an error (for example #DIV/0! or #NAME? or #Ref!).

So, I'd suggest that you test the cell's .Value first before you evaluate it:

For Each cell_search In Rng
    If IsError(cell_search.Value) Then
        'What should happen in this case?
    Else
        If (cell_search.Value) = "Not in Range" Then
            If del Is Nothing Then
                Set del = cell_search
            Else: Set del = Union(del, cell_search)
            End If
        End If
    End If
Next cell_search

Please let me know if this resolved the problem for you.

Upvotes: 1

Related Questions