vlookup problem???

[Guest]

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

 

[JE McGimpsey]

One way:

Public Sub Find1()
Range("C18:G18").Value = Evaluate( _
"=VLOOKUP(A18,A344:AR558,{7,10,13,16,19}, FALSE)")
End Sub

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.