Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
vlookup array in excel VBA
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"Lolly" 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 |
#6
|
|||
|
|||
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
how do i get mutiple values using vlookup in excel, lookup value . | Excel Discussion (Misc queries) | |||
How Do I open an excel file without Excel Viewer support | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
Excel Problem: VLookup | Excel Worksheet Functions |