[Solved] Compare 2 cells in different sheets in VBA(Excel 2010)

by


You can search a range for a value using Range.Find

Range.Find returns Nothing if no match is found or a Range if a match is found.

You can check if two objects refer to the same object using the is operator.

Here is an example:

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
        End If
    Next
End Sub

6

solved Compare 2 cells in different sheets in VBA(Excel 2010)