I want to write a function that returns all the j-th elements of i given items. The items contain single cells, ranges of cells or both.
While it is possible to return all elements (test1), the every first elements (test2), i fail to return the every second (or above) elements.
Given a Excel-Table
B C D
2 X 1 333
3 X 2 666
4 Z 3 999
=test1((B2;C2;D2);B3:D3;(B4:C4;D4))
returns X 1 333 Y 2 666 Z 3 999
=test2((B2;C2;D2);B3:D3;(B4:C4;D4))
returns X Y Z
But =test3((B2;C2;D2);B3:D3;(B4:C4;D4))
returns Y 2 3
, which is wrong. It should return 1 2 3
.
The code of the VBA-functions is the following:
Function Test1(ParamArray argArray() As Variant)
' return all elements of all items = OK
For Each outer_arg In argArray
For Each inner_arg In outer_arg
Test1 = Test1 & " " & inner_arg
Next inner_arg
Next outer_arg
End Function
Function Test2(ParamArray argArray() As Variant)
' return only the 1st elemtent of each item = OK
For Each outer_arg In argArray
Test2 = Test2 & " " & outer_arg(1)
Next outer_arg
End Function
Function Test3(ParamArray argArray() As Variant)
' return only the 2nd elemtent of each item = FAILS
For Each outer_arg In argArray
Test3 = Test3 & " " & outer_arg(2)
Next outer_arg
End Function
How do I address specific elements correctly?
Thank you @TimWilliams for showing me, what I misunderstood and what the cornercase (separate ranges) was. I wrote a solution, which loops over all elements with a simple counter. Works for me.
Function Test4(nmbr, ParamArray argArray() As Variant)
' return only the j-th argument (nmbr) of each element = OK
For Each outer_arg In argArray
cnt = 1
For Each inner_arg In outer_arg
If cnt = nmbr Then
Test4 = Test4 & " " & inner_arg.Value
End If
cnt = cnt + 1
Next inner_arg
Next outer_arg
End Function