Excel VBA Type mismatch error

0
votes

I am trying to compare the values from one column of one sheet, with the values of another column of a different sheet, same workbook, though. It steps through each cell in the other column, and if the cell value, a string, does not exist in sheet2, then the row from sheet1 is copied over to sheet3. You can think of it like I'm comparing two arrays. I want to see if there are any values in Array1 that do not appear in Array2, and if they do not appear in Array1, the value is copied into Array3.

My main issue is I'm getting a type-mismatch error in line 5. The values contain strings. I am fairly new at Excel VBA and am trying to learn it on the fly. Any help would be greatly appreciated.

Sub search()
Dim count As Integer
count = 0

For Each i In Worksheets("Sheet1").Range("C2:C4503")
    Set first_cell = Worksheets("Sheet1").Cells(i, 3) <-- Mismatch eror

    For Each j In Worksheets("Sheet2").Range("X2:X4052")
        Set second_cell = Worksheets("Sheet2").Cells(j, 24)
        If second_cell = first_cell Then Exit For
    Next j

    count = count + 1
    Set Worksheets("Sheet3").Cells(count, 1) = Worksheets("Sheet1").Cells(j, 1).Select
Next i

End Sub

2
excelvba
If you want the value then use the .Value or .Value2 property. Cells() returns a range. - John Alexiou
Are the values ordered in any way, or can a value from sheet1 be anywhere on sheet2? - John Alexiou
You're using i in an ambiguous way: first, as a range item in the For each i, and then as an integer in Cells(i, 3). - chuff
The values aren't ordered. EAch sheet has a column with values and I want to see if a value from sheet1 appears on sheet2. The value will only appear in the specific column. - user2460606

2 Answers

1
votes
Sub Search()

Dim rowNum As Long
Dim i As Range, f As Range

    rowNum = 1

    For Each i In Worksheets("Sheet1").Range("C2:C4503").Cells

        If Len(i.Value) > 0 Then
            'search for value on sheet2
            Set f = Worksheets("Sheet2").Range("X2:X4052").Find( _
                             i.Value, , xlValues, xlWhole)

            If f Is Nothing Then
                'not found: copy row from sheet1>sheet3
                i.EntireRow.Copy Worksheets("Sheet3").Cells(rowNum, 1)
                rowNum = rowNum + 1
            End If
        End If

    Next i

End Sub
0
votes

The following:

For Each i In Worksheets("Sheet1").Range("C2:C4503")
    ...
Next i

iterates through the cells in the specified range; i is a Range object representing the current cell.

You are using it as in integer index in the following line:

Set first_cell = Worksheets("Sheet1").Cells(i, 3)

Hence the Type Mismatch.