"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