CODEWITHSUNDEEP
vbaexcel
Roi patrick Florentino
Roi patrick Florentino

Reputation: 177

Comparing 2 columns in 2 sheets and shows matching but does not show no match

Here's my code. This compares 2 columns from different sheets (2 sheets) and then reflect the answer in another sheets but it fails to show the data that has no match. I came across an error that says Run Time '91': Object variable r With block variable not set.

Here's the code. Please help.

Sub lookup()
Dim TotalRows As Long
Dim rng As Range
Dim i As Long

'Copy lookup values from sheet1 to sheet3
Sheets("Sheet1").Select
TotalRows = ActiveSheet.UsedRange.Rows.Count
Range("A1:A" & TotalRows).Copy Destination:=Sheets("Sheet3").Range("A1")

'Go to the destination sheet
Sheets("Sheet3").Select

For i = 1 To TotalRows
    'Search for the value on sheet2
    Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 1).Value)
    'If it is found put its value on the destination sheet
    If Not rng Is Nothing Then
        Cells(i, 2).Value = rng.Value
    Else
        Cells(i, 4).Value = rng.Value    <------------stops here
    End If
Next
End Sub

Upvotes: 0

Views: 72

Answers (2)

David Zemens
David Zemens

Reputation: 53623

Change this:

Cells(i, 4).Value = rng.Value

To this:

Cells(i, 4).Value = "Not found"

Upvotes: 1

waka
waka

Reputation: 3407

You are trying to assign Nothing to a cell if no match is found:

If Not rng Is Nothing Then
    Cells(i, 2).Value = rng.Value 'rng has a value
Else
    Cells(i, 4).Value = rng.Value 'rng has NO value, ie is Nothing
End If

This is not possible and that's why you get the error.

Upvotes: 2

Related Questions