VBA Check If Variable Exists in a Column

[ckp90c]

Hi all,

Just a question about checking if a variable exists in a certain column.

I want to find out if a variable is located in Column A in another sheet.

I have an If statement that currently says below...

If VARIABLE <> "" Then

Output.Cells(m, 1) = xxx
Output.Cells(m, 2) = yyy
Output.Cells(m, 3) = zzz


However, I would rather it check if the Variable is in a range of cells instead.

Is there an easy way to do this?

Thanks

 

[Joe4]

Welcome to the Board!

Take a look at using FIND.
See: Find value in Range, Sheet or Sheets with VBA

 

[JLGWhiz]

Depends or how your variable is in the cells and what the data type is in some cases. But here are a couple of ways.

If Application.CountIf(Range("A:A"), variable) > 0 Then 'if it is a stand alone value
 MsgBox "It's there"
End If
Dim c As Range
For Each c In Range("A:A")
 If InStr(c.Value, variable) > 0 Then 'Embeded in other data
  MsgBox "It's there"
 End If
Next
End If