vlookup array in excel VBA

[Lolly]

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

[Bernie Deitrick]

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@discussions.microsoft.com> wrote in message
news:C404D91A-B1CD-4686-A915-EE255CFCD39D@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

[Harlan Grove]

Bernie Deitrick wrote...
>As an alternative, you can use worksheet formulas:
>
>Range("C150:H150").Formula = _
>"=Vlookup($A$21,$A$246:$P$345,column(E1),False)"

That E1 could be a maintenance headache over time. Granted it's longer
due to the need to use R1C1-style referencing, but it may be better in
the long run to use FormulaArray.

Range("C150:H150").FormulaArray = Application.ConvertFormula( _
Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlR1C1 _
)

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

If the goal is values rather than formulas,

Dim n As Long
n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
Range("C150:H150").Value = _
Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value

[Lolly]

Hi,
Harlan Grove
I tried both of your formulas.
Range("C150:H150").FormulaArray = Application.ConvertFormula( _
> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
> FromReferenceStyle:=xlA1, _
> ToReferenceStyle:=xlR1C1 _
)

Range("C150:H150").Value = Range("C150:H150").Value

In the cells Instead of values I get following error
#VALUE in all 6 cells.


Dim n As Long
> n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
> Range("C150:H150").Value = _
> Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value

As soon as I run this I get Type mismatch error on first line n = ...

Please hepl me more
I would really appreciate that.

Regards
Sarita






"Harlan Grove" wrote:










> Bernie Deitrick wrote...
> >As an alternative, you can use worksheet formulas:
> >
> >Range("C150:H150").Formula = _
> >"=Vlookup($A$21,$A$246:$P$345,column(E1),False)"
>
> That E1 could be a maintenance headache over time. Granted it's longer
> due to the need to use R1C1-style referencing, but it may be better in
> the long run to use FormulaArray.
>
> Range("C150:H150").FormulaArray = Application.ConvertFormula( _
> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
> FromReferenceStyle:=xlA1, _
> ToReferenceStyle:=xlR1C1 _
> )
>
> >If you want values rather than formulas, simply add the line:
> >Range("C150:H150").Value = Range("C150:H150").Value
>
> If the goal is values rather than formulas,
>
> Dim n As Long
> n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
> Range("C150:H150").Value = _
> Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
>
>

[Harlan Grove]

"Lolly" <Lolly@discussions.microsoft.com> wrote...
>I tried both of your formulas.
>>Range("C150:H150").FormulaArray = Application.ConvertFormula( _
>> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
>> FromReferenceStyle:=xlA1, _
>> ToReferenceStyle:=xlR1C1 _
>>)
>>
>>Range("C150:H150").Value = Range("C150:H150").Value
>
>In the cells Instead of values I get following error
>#VALUE in all 6 cells.

I just ran the following macro


Sub foo()
Range("C150:H150").FormulaArray = Application.ConvertFormula( _
Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlR1C1 _
)
Range("C150:H150").Value = Range("C150:H150").Value
End Sub

with A21 containing 5 and A246:P345 containing the formula

=ROW()-245+(COLUMN()-1)*1000

It resulted in C150:H150 containing

{4005,5005,6005,7005,8005,#N/A}

I should have caught the #N/A in my original response. You're putting an
array of 5 entries into a range of 6 cells, at least that's what your
original macro statement was trying to do. That will ALWAYS result in the
6th cell containing #N/A. However, as long as there aren't errors in the
ranges you're using, the macro statements above won't return #VALUE! in
C150:H150.

What's your ENTIRE macro code, and what's in your A21 and A246:P345 ranges?

>>Dim n As Long
>>n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
>>Range("C150:H150").Value = _
>> Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
>
>As soon as I run this I get Type mismatch error on first line n = ...
....

That indicates you don't have a matching entry for A21 in A246:A345. If that
could be the case, use


Dim n As Variant
n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
If Not IsError(n) Then
Range("C150:H150").Value = _
Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
Else
MsgBox CStr(Range("A21").Value) & " doesn't appear in A246:A345"
End If

[Lolly]

Hi,
Harlan Grove,

My macro code is as follows,

Dim lastrwo as Long, s1 as variant, s2 as variant, s3 as variant, s4 as
variant, s5 as variant
Dim i as long , col as long, rw as long


Application.Worksheets("Sheet1").Select

Lastrow = Cells(Rows.Count, 1).End(xlup).Row

s3 = Cells(2, 1).Value
s4 = Cells(2, 2).Value
s5 = Cells(2, 3).Value

Col = 4
rw = 251
For i = 2 to lastrow
If cells(i, 1) = s3 and Cells(i, 2) = s4 and Cells(i, 3) = s5 Then
Col = col + 1
Else

Col = 5
rw = rw + 1
s3 = Cells(i,1)
s4 = Cells(i, 2)
s5 = Cells(i, 3)
End if

Worksheets("Sheet2").Cells(rw, col) = Cells(i, 7).Value
Worksheets("Sheet2").Cells(rw, 2) = Cells(i, 1).Value
Worksheets("Sheet2").Cells(rw, 3) = Cells(i, 2).Value
Worksheets("Sheet2").Cells(rw, 4) = Cells(i, 3).Value

Next i

In Col A I have the values which I neec to match and then get the matching
Values.

After this I use your Formula as follows:

Range("C150:H150").FormulaArray = Application.ConvertFormula( _
> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9, 10},0)", _
> FromReferenceStyle:=xlA1, _
> ToReferenceStyle:=xlR1C1 _
> )
> Range("C150:H150").Value = Range("C150:H150").Value

But still I get #Value in he cell as error

I want the actual values in the cell.
When I use this Formula
Range("C150").Value = WorksheetFuncion.Vlookup(Range("A20"),
Range("A246:P345"), 4, False).
Then I get the Value

But I have thousands to do it's very lengthy process and Also ther is a
maintenance problem.

If you could help me further I would appreciate that.

Thanx a lot

Kittie


"Harlan Grove" wrote:

> "Lolly" <Lolly@discussions.microsoft.com> wrote...
> >I tried both of your formulas.
> >>Range("C150:H150").FormulaArray = Application.ConvertFormula( _
> >> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
> >> FromReferenceStyle:=xlA1, _
> >> ToReferenceStyle:=xlR1C1 _
> >>)
> >>
> >>Range("C150:H150").Value = Range("C150:H150").Value
> >
> >In the cells Instead of values I get following error
> >#VALUE in all 6 cells.
>
> I just ran the following macro
>
>
> Sub foo()
> Range("C150:H150").FormulaArray = Application.ConvertFormula( _
> Formula:="=VLOOKUP($A$21,$A$246:$P$345,{5,6,7,8,9},0)", _
> FromReferenceStyle:=xlA1, _
> ToReferenceStyle:=xlR1C1 _
> )
> Range("C150:H150").Value = Range("C150:H150").Value
> End Sub
>
> with A21 containing 5 and A246:P345 containing the formula
>
> =ROW()-245+(COLUMN()-1)*1000
>
> It resulted in C150:H150 containing
>
> {4005,5005,6005,7005,8005,#N/A}
>
> I should have caught the #N/A in my original response. You're putting an
> array of 5 entries into a range of 6 cells, at least that's what your
> original macro statement was trying to do. That will ALWAYS result in the
> 6th cell containing #N/A. However, as long as there aren't errors in the
> ranges you're using, the macro statements above won't return #VALUE! in
> C150:H150.
>
> What's your ENTIRE macro code, and what's in your A21 and A246:P345 ranges?
>
> >>Dim n As Long
> >>n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
> >>Range("C150:H150").Value = _
> >> Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
> >
> >As soon as I run this I get Type mismatch error on first line n = ...
> ....
>
> That indicates you don't have a matching entry for A21 in A246:A345. If that
> could be the case, use
>
>
> Dim n As Variant
> n = Application.Match(Range("A21").Value, Range("A246:A345"), 0)
> If Not IsError(n) Then
> Range("C150:H150").Value = _
> Range("A246:P345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
> Else
> MsgBox CStr(Range("A21").Value) & " doesn't appear in A246:A345"
> End If
>
>
>