vlookup array in excel VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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
 
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)"
Click to expand...

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
Click to expand...

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
 
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 _
Click to expand...
)

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
Click to expand...

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






:
 
Lolly said:
I tried both of your formulas.

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

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?
As soon as I run this I get Type mismatch error on first line n = ...
Click to expand...
....

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
 
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
Click to expand...

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
 
Lolly wrote...
....
After this I use your Formula as follows:
Click to expand...

[unquoted]
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
Click to expand...

I didn't really need to see the preceding macro code. What I wanted to
see was how you had included the code I proposed in your macro, and it
would have been better had you done what I asked and posted your
*ENTIRE* macro including the added code I had proposed rather than
second guessing and providing scraps of macro code.

If you're using the code immediately above with the '>' as the first
character in the 2nd through 6th lines, then that's the problem. I had
thought they were just misquoting in your follow-up, but now I'm
beginning to believe they may be what you're trying to use.

I'll repeat from my first response. Try

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 _
)

Note: NO '>' chars at the beginning of any line. Followed by

Range("C150:H150").Value = Range("C150:H150").Value
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
Click to expand...
....

You realize *YOUR* original formula was

Range("C150:H150").Value =
Application.WorksheetFunction.Vlookup(Range("A21"),Range("A2­46:P345"),

columns(5,6,7,8,9),False)

Now *YOU* have changed the first argument to VLOOKUP from A21 to A20.
If your original formula had included a typo, it's up to *YOU* to
modify any code or formulas in any responses that duplicate any & all
mistakes in your original. If you really mean to use A20 rather than
A21, then you need to change the code I'd originally provided to

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