vba - vlookup on data stored in array

[malleshg24]

Hi Team,

I am trying to apply vlookup formula in E2:E3, by storing data in array.
please can someone assist, new in array.

below are my attempted code. :help


Sub vlookup_witharray()
Dim arr1() As Variant
Dim arr2() As Variant
arr1 = Range("d2:d3").Value
arr2 = Range("a1").CurrentRegion
Dim arr3() As Variant
Dim i As Long
For i = 1 To 3
arr3(i, 1) = WorksheetFunction.VLookup(arr1(i, 1), arr2, 2, False)
Range("E" & i).Value = arr3(i, 1)
Next i
'Range("E" & i).Value = arr3(i, 1)
End Sub

Regards,
Mallesh

[p45cal]

try:
Sub vlookup_witharray()
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, zz

arr1 = Range("d2:d3").Value
arr3 = Range("e2:e3").Value
arr2 = Range("a1").CurrentRegion

Dim i As Long
For i = 1 To 2
arr3(i, 1) = WorksheetFunction.VLookup(arr1(i, 1), arr2, 2, False)
zz = WorksheetFunction.VLookup(arr1(i, 1), arr2, 2, False)
Range("E" & i + 1).Value = zz 'arr3(i, 1)
Next i
Range("e2:e3").Value = arr3
End Sub
or more generally:
Sub vlookup_witharray2()
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, zz, i As Long

arr1 = Range("d2:d3").Value
ReDim arr3(1 To UBound(arr1), 1 To 1)
arr2 = Range("a1").CurrentRegion

For i = 1 To UBound(arr1)
arr3(i, 1) = WorksheetFunction.VLookup(arr1(i, 1), arr2, 2, False)
Next i

Range("e2").Resize(UBound(arr1)).Value = arr3
End Sub

[malleshg24]

Hi p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal),
:bow:
Thanks a lot It worked for me as expected,

But if I change any look up value, it throws error. if no data found I want the result #N/A, Thanks.

plz assist. Thanks


Regards,
Mallesh

[p45cal]

Change instances of WorsheetFunction to Application.

Note that the first macro is a combination of two methods of getting the data to the sheet shown, in real use it would be either:
Sub vlookup_witharray()
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, zz

arr1 = Range("d2:d3").Value
arr3 = Range("e2:e3").Value
arr2 = Range("a1").CurrentRegion

Dim i As Long
For i = 1 To 2
arr3(i, 1) = Application.VLookup(arr1(i, 1), arr2, 2, False)
Next i
Range("e2:e3").Value = arr3
End Sub
or
Sub vlookup_witharray()
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, zz

arr1 = Range("d2:d3").Value
arr3 = Range("e2:e3").Value
arr2 = Range("a1").CurrentRegion

Dim i As Long
For i = 1 To 2
Range("E" & i + 1).Value = Application.VLookup(arr1(i, 1), arr2, 2, False)
Next i
End Sub
but I still prefer the more general version:
Sub vlookup_witharray2()
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, zz, i As Long

arr1 = Range("d2:d3").Value
ReDim arr3(1 To UBound(arr1), 1 To 1)
arr2 = Range("a1").CurrentRegion

For i = 1 To UBound(arr1)
arr3(i, 1) = Application.VLookup(arr1(i, 1), arr2, 2, False)
Next i

Range("e2").Resize(UBound(arr1)).Value = arr3
End Sub