Unique Values into Arrays

[Forestq]

Hi,

I want to fill Tab pairs, like:
[0] = Eng, 2
[1] = De, 3
[2] = PL, 4
[3] = FR, 5
etc...

I know how to only add first value.

How to
1) add second value and
2) check if the first element is into Tab? (because value like Eng, De....need to be unique into Tab)

Dim CostCatTab() As Variant
Dim tabSize as Long
For b = 3 To 20
	CostCategory = wsSum.Cells(10, b).Value
	Value = wsSum.Cells(11, b).Value                                         
		
		tabSize = tabSize + 1
        ReDim Preserve CostCatTab(tabSize)
        CostCatTab(tabSize) = CostCategory             
Next

 

[baitmaster]

We're working with an array of results, which can be multi-dimensional, which we'll take advantage of. You can resize your array as you are already doing, but can only resize the last dimension.

The first "cell" in the array is cell 0, so when you redim it you may want to start at cell 1 instead of cell 0. I'll consider this as it's good practice

So to answer your first point, the following code would do it

For b = 3 To 20
    CostCategory = wsSum.Cells(10, b).Value
    Value = wsSum.Cells(11, b).Value
    
    tabSize = tabSize + 1
    ReDim Preserve CostCatTab(1 To 2, 1 To tabSize)
    CostCatTab(1, tabSize) = CostCategory
    CostCatTab(2, tabSize) = "[your other item here]"
Next b

Note that the requirement to always expand only the second dimension means that your 2 dimensional array may need to be transposed. You can use e.g.

range("A1").resize(ubound(myArray,1),ubound(myarray,2)) = myArray

to write the results, or

range("A1").resize(ubound(myArray,1),ubound(myarray,2)) = application.transpose myArray

if you need to transpose it (so long as neither rows nor columns exceed 65536)

Your second point requires you either to loop through the array and check what's already in it, or to use a scripting dictionary. The code may be a little unfamiliar at first but you'll benefit from understanding them

 

[Forestq]

thank you!