i have this code that checks if the value from h22 matches from entries in column i. how can i change the codes that it should check if h22 is an exact match with the last value on column i?

Option Explicit

Sub Macro()

    Dim oWs As Worksheet
    Dim rSearchRng As Range
    Dim lEndNum As Long
    Dim vFindVar As Variant
    
    Set oWs = ActiveWorkbook.Worksheets("Sheet2")
    lEndNum = oWs.Range("I1").End(xlDown).Row
    Set rSearchRng = oWs.Range("I1:I" & CStr(lEndNum))
    Set vFindVar = rSearchRng.Find(Range("H22").Value)
    
    If Not vFindVar Is Nothing Then
        MsgBox "Match"
    Else
        MsgBox "No Match Found"
    End If
    
End Sub

CodePudding user response：

Check Cell Against Last Cell in Column

• Checks if the value of the last non-empty cell in column I of worksheet Sheet2 is equal to the value in cell H22 of worksheet Sheet1 and shows a message box.

Option Explicit

Sub CheckCellAgainstLastCell()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
 
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
    Dim slCell As Range: Set slCell = sws.Cells(sws.Rows.Count, "I").End(xlUp)
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    Dim dCell As Range: Set dCell = dws.Range("H22")
    
    If CStr(dCell.Value) = CStr(slCell.Value) Then
        MsgBox "It's a match.", vbInformation
    Else
        MsgBox "It's not a match.", vbCritical
    End If
    
End Sub