vlookup array in excel VBA

[Guest]

HI

I am using the following formula
Sub find()
Range("C150:H150").Value =
Application.WorksheetFunction.Vlookup(Range("A21"),Range("A246345"),
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

 

[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

 

[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("A246345").Offset(n - 1, 5 - 1).Resize(1, 5).Value

 

[Guest]

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("A246345").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]

I tried both of your formulas.

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 A246345 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 A246345 ranges?

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("A246345").Offset(n - 1, 5 - 1).Resize(1, 5).Value
Else
MsgBox CStr(Range("A21").Value) & " doesn't appear in A246:A345"
End If

 

[Guest]

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("A246345"), 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]

Lolly wrote...
....

After this I use your Formula as follows:

[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

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("A246345"), 4, False).
Then I get the Value

....

You realize *YOUR* original formula was

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

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