Prev: Vlookups array function by Alan Beban
Next: how to graph a horsepower curve in excel (horsepower vs. rpm)
From: Lolly on 18 Feb 2005 07:45
HI

I am using the following formula
Sub find()
Range("C150:H150").Value =
Application.WorksheetFunction.Vlookup(Range("A21"),Range("A246:P345"),
columns(5,6,7,8,9),False)
This way I have lot of rows in which I need to use array or loop .
end sub
Error which I am getting
Unable to get the Vlookup property of the Worksheet Function Class.

Is there a way where either I can create a loop where I can put a variable
name in place of columns .

Any help is highly appreciated.
Thanx in advance

--
Kittie
From: "Bernie Deitrick" <deitbe @ consumer dot on 18 Feb 2005 10:52
Kittie,

As an alternative, you can use worksheet formulas:

Range("C150:H150").Formula = _
"=Vlookup($A$21,$A$246:$P$345,column(E1),False)"

If you want values rather than formulas, simply add the line:
Range("C150:H150").Value = Range("C150:H150").Value

HTH,
Bernie
MS Excel MVP

"Lolly" <Lolly(a)discussions.microsoft.com> wrote in message
news:C404D91A-B1CD-4686-A915-EE255CFCD39D(a)microsoft.com...
> HI
>
> I am using the following formula
> Sub find()
> Range("C150:H150").Value =
> Application.WorksheetFunction.Vlookup(Range("A21"),Range("A246:P345"),
> columns(5,6,7,8,9),False)
> This way I have lot of rows in which I need to use array or loop .
> end sub
> Error which I am getting
> Unable to get the Vlookup property of the Worksheet Function Class.
>
> Is there a way where either I can create a loop where I can put a variable
> name in place of columns .
>
> Any help is highly appreciated.
> Thanx in advance
>
> --
> Kittie


 | 
Pages: 1
Prev: Vlookups array function by Alan Beban
Next: how to graph a horsepower curve in excel (horsepower vs. rpm)