Using VBA to LOOKUP

[ukdane]

I have a variable which contains a string (to letters, for example DE)
I need some code that will take the variable, and look at an array (Worksheet = "Sheet2" Range ="A1:A249") If it finds the letters then do something, if not then do something else.

Whats the best way to solve this? I'm guessing it is part of an If statement, but I'm not sure how to try to match/find the variable.
Also, I don't know if this will be relevant, but it will be part of a loop, as the variable will need to constantly change.

Thanks for your help.

[Simon Lloyd]

You should supply your code or workbook as you probably want a CASE statement, however you asked for VLOOKUP so...[VBA]Dim MyVar As String
Dim MyVarResult
MyVarResult = Application.WorksheetFunction.VLookup(MyVar, Sheets("Sheet2").Range("A1:A249"), 2, False)
If MyVarResult <> "" Then
'DO SOMETHING
Else
'DO SOMETHING ELSE
End If[/VBA]MyVar would be your variable. Take a look at Case in conjunction with the worksheet selection_change event.

[Bob Phillips]

[vba]

Dim myVar As String
Dim cell As Range

myVar = "DE"
Set cell = Worksheets("Sheet2").Range("A1:A249").Find(myVar)
If Not cell Is Nothing Then

MsgBox "do something"
End If
[/vba]

[ukdane]

Thanks both for your replies.
I went for the Lookup solution.
However, I've been able to apply the Find solution to another workbook.
Question using the code below, how can I get the cell reference if the find is successfull?

[vba]
Dim myVar As String
Dim cell As Range
Dim mycellref as string

myVar = "DE"
Set cell = Worksheets("Sheet2").Range("A1:A249").Find(myVar)
If Not cell Is Nothing Then
mycellref = cell.address 'provide my cell reference
MsgBox "do something"
End If [/vba]


Never mind, got it

[Bob Phillips]

Aren't you already doing just that?

[ukdane]

I am now thanks, I edited the code, when I wrote that I had found the solution, to show the solution.

Thanks for your help. :-)