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 |
#7
|
|||
|
|||
Lolly wrote...
..=2E. After this I use your Formula as follows: [unquoted] Range("C150:H150").FormulaArray =3D Application.ConvertFormula( _ Formula:=3D"=3DVLOOKUP($A$21,$A$246:$P$345,{5,6,7, 8,9, 10},0)", _ FromReferenceStyle:=3DxlA1, _ ToReferenceStyle:=3DxlR1C1 _ ) Range("C150:H150").Value =3D 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 =3D Application.ConvertFormula( _ Formula:=3D"=3DVLOOKUP($A$21,$A$246:$P$345,{5,6,7, 8,9,10},0)", _ FromReferenceStyle:=3DxlA1, _ ToReferenceStyle:=3DxlR1C1 _ ) Note: NO '' chars at the beginning of any line. Followed by Range("C150:H150").Value =3D Range("C150:H150").Value I want the actual values in the cell. When I use this Formula Range("C150").Value =3D WorksheetFuncion.Vlookup(Range("A20"), Range("A246:P345"), 4, False). Then I get the Value ..=2E. You realize *YOUR* original formula was Range("C150:H150").Value =3D Application.WorksheetFunction.Vlookup(Range("A21") ,Range("A2=AD46: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 =3D Application.ConvertFormula( _ Formula:=3D"=3DVLOOKUP($A$20,$A$246:$P$345,{5,6,7, 8,9,10},0)", _ FromReferenceStyle:=3DxlA1, _ ToReferenceStyle:=3DxlR1C1 _ ) |
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 |