vba - If...Then statement for "if value is present in a specified column"

Question

I'm trying to write an If...Then condition such that:

If the value of cell A3 in Sheet1 is present in column A of Sheet2 then...

I have tried with this code but it doesn't work (Error) :

If Worksheets("Sheet1").Range("A3") = Worksheets("Sheet2").Range("A:A") Then
...

Answer 1

I'm not quite sure why you're getting an error here, but your If..Then condition is definitely not working the way you're hoping/expecting it will.

You're trying to compare a single value (the value of A3 in Sheet1) to an entire column (column A in Sheet2), which is definitely not the same as looking to see if that value is in that column.

You can use the Range.Find method to determine whether a value is present in a given range, as shown below.

Dim varFindThis As Variant
Dim rngLookIn As Range

varFindThis = Worksheets("Sheet1").Range("A3")
Set rngLookIn = Worksheets("Sheet2").Range("A:A")

If Not rngLookIn.Find(varFindThis, LookIn:=xlValues) Is Nothing Then
    ...
End If

See this MSDN page for more details on the function and how it can be used.