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