Lookup Function in Macro (VBA)

[smartguy0753]

Hi,

I am trying to 'Record a Macro' in the new Excel 2007 that includes the
LOOKUP Function. However, when I finish recording and try to run it, I get
errors. From what I understand, it is because it has a "{" in the formula.
However, that's just how it is entered. (basically, i want it to look in the
specified cell and place the corresponding value in the selected cell)

Does anyone have a solution to this so that the Macro will run?

Thanks.

Here is the idea of the LOOKUP...
=LOOKUP(I2,{"1","3","5"},{"help","this","the"})

 

[Bernie Deitrick]

All right smart guy,

If you are trying to add the formula to a cell:

ActiveCell.Formula = _
"=LOOKUP(I2,{""1"",""3"",""5""},{""help"",""this"",""the""})"

IF you are trying to get the value of that formula:

Dim myVar As String
myVar = Application.WorksheetFunction.Lookup(Range("I2").Value, _
Array("1", "3", "5"), Array("help", "this", "the"))

Note that the formula is looking for the string value 1 and not the number 1
in cell I2.

HTH,
Bernie
MS Excel MVP

 

[Jacob Skaria]

Somthing like this...Try and feedback..

Dim arrLookup As Variant
Dim arrResult As Variant

arrLookup = Array("1", "3", "5")
arrResult = Array("help", "this", "the")
MsgBox WorksheetFunction.Lookup("5", arrLookup, arrResult)


If this post helps click Yes