VBA if cell on sheet 1 matches a cell in column I on sheet 2 then?

Question

I'm trying to use a vba if statement to check if my cell H22 on sheet 1 matches a cell in column I on sheet 2.

can someone please show me where im going wrong? Thanks

    If Range("H22").Value Like Worksheets("Sheet 2").Range("I1").Column Then

MsgBox "Match"

Else

MsgBox "No Match Found"

End If

It keeps telling me there is no match found so im guessing it cant find the matching cell value in column I on sheet 2

Answer 1

Worksheets("Sheet 2").Range("I1").Column will return "9" always because Column I is the 9th column. You are comparing the value in Range("H22") to "9". Unless H22 is "9" you will get "No Match Found".

Try, instead, using either the .find method of the range object or loop through your column looking for values.

Answer 2

The problem with your code is that you're comparing an individual cell to the entire 'I' column on Sheet 2. What you could do to fix this is use a find to see if a matching value exists. If it does then return match.

Here's some code on how to do this, I would also define Range("H22").Value so that you know exactly where it is coming from.

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

Here is documentation on the find method.

Answer 3

If you simply want to check for the existence of the value then bring in a low-level lookup function with Application.Match() or WorksheetFunction.Match() and depend on whether it returns an error. Something like this should suffice.

If IsError(Application.Match(Range("H22").Value, Sheets("Sheet 2").Columns("I"), 0)) Then
    MsgBox "No Match Found"
Else
    MsgBox "Match"
End If

If you wanted to look for more than one then Application.CountIf() would return a number between 0 and something higher.

BTW, there is not usually a space between Sheet and 2.

Answer 4

Here is a typical example of trying to match a cell value against the values in a column:

Sub james()
    Dim v As Variant, r As Range, rWhere As Range
    v = Sheets("Sheet1").Range("H22").Value
    Set rWhere = Sheets("Sheet2").Range("I:I")
    Set r = rWhere.Find(what:=v, After:=rWhere(1))
    If r Is Nothing Then
        MsgBox "No match found"
    Else
        MsgBox "Match found"
    End If
End Sub