I have a problem to vlookup values from one array into another.

Arrays are:

My code results in Error 2042:

Dim arr9 As Variant
ReDim arr9(LBound(arr7)   UBound(arr7))
i = 0
For i = LBound(arr7) To UBound(arr7) Step 1
arr9(i) = Application.VLookup(Left(arr7(i), 5) & "*", Arr, 1, 0)
Next i

What I need is to find items from arr7, i.e. arr7(0) = zch20 in Arr and assign corresponding value from arr to arr9. Just first 5 characters need to be vlookuped since they are mutual characters for both arr and arr7 but full name string needs to be inserted to arr9.

Thank you.

CodePudding user response：

VLOOKUP is a worksheet function, for use with ranges of cells (or worksheet tables) but not for VBA arrays.

A comparable VBA function that you might have more luck with is Filter.

Filter(sourcearray, match, [ include, [ compare ]])

More info:

• Documentation: Filter function
• Microsoft support: VLOOKUP: When and how to use it (video)
• AutomateExcel.com: VBA Search for (Find) Value in Array
• Stack Overflow: Wildcard search in array