PDA

View Full Version : Using VBA to LOOKUP



ukdane
04-15-2009, 03:20 AM
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
04-15-2009, 04:24 AM
You should supply your code or workbook as you probably want a CASE statement, however you asked for VLOOKUP so...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 IfMyVar would be your variable. Take a look at Case in conjunction with the worksheet selection_change event.

Bob Phillips
04-15-2009, 06:27 AM
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

ukdane
04-16-2009, 05:44 AM
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?


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


Never mind, got it

Bob Phillips
04-16-2009, 05:58 AM
Aren't you already doing just that?

ukdane
04-16-2009, 07:18 AM
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. :-)