type mismatch when referencing value of cells to values in another sheet

Question

I want to delete entire row if value in column B (sheet "Track") is the same as value in column B (sheet "Active"). But run time error 13 (type mismatch) always occur even though both values I refer are string type

Here is the code:

Sub delete_row()
Dim active As Worksheet: Set activeSH = ThisWorkbook.Sheets("Active")
Dim Tracksheet As Worksheet: Set KPI = ThisWorkbook.Sheets("Track")
Dim i As Integer
Dim name As String

    With Tracksheet
         For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 4 Step -1
         name = .Range("B" & i).Value 

            'Here I loop through each value in col B of Track sheet 
            'and reference it to values in col B of sheet "active"       
            If name = active.Range("B:B").Value Then 'this line where run time error 13 (type mismatch occurs)

                .Rows(i).EntireRow.Delete

            Else
            End If
        i = i - 1
        Next i
    End With
 End Sub

I really appreciate your help!

The error is occuring because you are comparing a single string `name` to an entire range of cell values -- all of column B. The line should be `If name = active.Range("B" & i).Value Then` — PeterT, May 30 '19 at 02:16
If name = active.Range("B:B").Value - this doesn't make sense. Use `FIND` to search for matching values. See for example: https://stackoverflow.com/questions/22464631/perform-a-find-within-vba-from-the-bottom-of-a-range-up — Michal Rosa, May 30 '19 at 02:44
I only posted that because your code was unusable in it's current state. You had lines of comments without a `'` so when I copied it, it didn't run, also missing a `"` after `("TRACK` I submitted an edit, looks like you got an answer already, but I was just working on making your code work. — FreeSoftwareServers, May 30 '19 at 03:08
I agree with @MichalRosa. Use `.Find` Looping to find a value is the inefficient way of doing it if the dataset is large. You may want to see [THIS](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) — Siddharth Rout, May 30 '19 at 04:30

Mikku · Answer 1 · 2019-05-30T03:09:15.170

1

You might wanna try something like this:

Sub delete_row()

Dim active As Worksheet
Dim Tracksheet As Worksheet
Dim i As Integer
Dim name As String
Dim cl As Range

Set active = ThisWorkbook.Sheets("Active")
Set Tracksheet = ThisWorkbook.Sheets("Track")

    With Tracksheet

         For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 4 Step -1

                 name = .Range("B" & i).Value

                    For Each cl In active.Range("B1:B100")

                        If name = cl.Value Then

                            .Rows(i).EntireRow.Delete

                        End If

                    Next cl

                    i = i - 1

        Next i

    End With

 End Sub

You can change the Range B1:B100 as per your requirement.

edited May 30 '19 at 03:09

answered May 30 '19 at 02:57

Mikku

6,538
3
15
38

(You forgot the close quote on `Sheets("Track)`) – BruceWayne May 30 '19 at 03:01
1

Thanks @BruceWayne .... I overlooked the code. Even the setting of worksheet was wrong! – Mikku May 30 '19 at 03:10
1

No worries! I've also found sometimes simply pasting code to SO helps, since the "coloring" is off if you forget a close parenthesis/quote/etc. That's how I quickly noticed the lack of the closing `"`. – BruceWayne May 30 '19 at 03:19
@BruceWayne ... Great! Learnt something new today :) – Mikku May 30 '19 at 03:32

type mismatch when referencing value of cells to values in another sheet

1 Answers1