Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup problem???
My code is like this
Sub find() Dim i as long varr = Evaluate("Vlookup(A18, A344:AR558, {7,10,13,16,19}, False") If IsArray(varr) then For i = Lbound(varr) to Ubound(Varr) Range("C18:G18").Value = Varr(i) Next End if End Sub Error which I get is you can't changea part of array. I want the values found from array to be put in cells C18, D18, E18, F18, G18 Any help is highly appreciated Thanks a lot in advance -- Kittie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup problem???
One way:
Public Sub Find1() Range("C18:G18").Value = Evaluate( _ "=VLOOKUP(A18,A344:AR558,{7,10,13,16,19}, FALSE)") End Sub In article , "Lolly" wrote: My code is like this Sub find() Dim i as long varr = Evaluate("Vlookup(A18, A344:AR558, {7,10,13,16,19}, False") If IsArray(varr) then For i = Lbound(varr) to Ubound(Varr) Range("C18:G18").Value = Varr(i) Next End if End Sub Error which I get is you can't changea part of array. I want the values found from array to be put in cells C18, D18, E18, F18, G18 Any help is highly appreciated Thanks a lot in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup problem???
Kittie,
How about Dim i As Long For i = 3 To 5 cels(18, i).Value = Application.VLookup(Range("A18"), Range("A344:AR558"), (i - 2) * 3 + 4, False) Next -- HTH RP (remove nothere from the email address if mailing direct) "Lolly" wrote in message ... My code is like this Sub find() Dim i as long varr = Evaluate("Vlookup(A18, A344:AR558, {7,10,13,16,19}, False") If IsArray(varr) then For i = Lbound(varr) to Ubound(Varr) Range("C18:G18").Value = Varr(i) Next End if End Sub Error which I get is you can't changea part of array. I want the values found from array to be put in cells C18, D18, E18, F18, G18 Any help is highly appreciated Thanks a lot in advance -- Kittie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup problem???
Just to add,
Here is just one more Sounds like at least one of the cells in C18:G18 are part of a multicell array formula. If so, you need to remove the array formula before you can update them unless the array formula encompasses C18:G18 alone. Then your original code could be: Sub find() Dim Varr As Variant Varr = Evaluate("Vlookup(A18, A344:AR558, {7,10,13,16,19}, False)") If IsArray(Varr) Then Range("C18:G18").Value = Varr End If End Sub Of course JE's is much more compact. -- Tom Ogilvy "Lolly" wrote in message ... My code is like this Sub find() Dim i as long varr = Evaluate("Vlookup(A18, A344:AR558, {7,10,13,16,19}, False") If IsArray(varr) then For i = Lbound(varr) to Ubound(Varr) Range("C18:G18").Value = Varr(i) Next End if End Sub Error which I get is you can't changea part of array. I want the values found from array to be put in cells C18, D18, E18, F18, G18 Any help is highly appreciated Thanks a lot in advance -- Kittie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup problem | Excel Worksheet Functions | |||
VLookup problem | Excel Worksheet Functions | |||
Vlookup Problem | Excel Worksheet Functions | |||
vlookup problem | Excel Programming | |||
problem with vlookup | Excel Programming |