Add one array to another array

[Foreverlearning]

Hi,

I am still just not getting it.
I have a range on one sheet which I have added to an array and another range on another sheet added to an array
If value in array matches value in 2nd array I want to add that value to a 3rd array.
Just getting stumped here.

This is what I have to date..

Sub Count_Names()
Dim vaRoster() As Variant, vaNames() As Variant, vaPNames() As Variant, vaCNames() As Variant
Dim ln As Long, lm As Long
Dim l As Long, m As Long, vaCount As Integer

    vaRoster = ActiveSheet.Range("C5:D24").Value
    vaNames = Sheets("Name_Matrix").Range("B5:C68").Value
    
    ReDim Preserve vaPNames(UBound(vaNames) + 1)
    
    For ln = LBound(vaNames, 1) To UBound(vaNames, 1)
        If vaNames(ln, 1) = "P" Then
            vaPNames() = vaNames(ln, 2)
        End If
    Next ln
    
    
    For l = LBound(vaRoster, 1) To UBound(vaRoster, 1)
        For m = LBound(vaRoster, 2) To UBound(vaRoster, 2)
            If vaRoster(l, m) <> "" Then vaCount = vaCount + 1
        Next m
    Next l

    MsgBox vaCount

Thanks for your time

Lionel

[StevenM]

ReDim Preserve vaPNames(UBound(vaNames) + 1)

At this point in your code, there is nothing to preserve.
vaNames is a 2D array, whereas you are only creating a 1D array.
The size of this 1D array is the size of the first dimension of vaNames plus one extra element.

vaPNames() = vaNames(ln, 2)

Shouldn't this be:

vaPNames(ln) = vaNames(ln, 2)

As for the rest, it is unclear to me what you are trying to accomplish.

[nilem]

maybe so

Sub Count_Names()
Dim vaRoster() As Variant, vaNames() As Variant, vaPNames() As Variant
Dim i As Long, vaCount As Long, x As Variant

'I have a range on one sheet which I have added to an array
vaRoster = ActiveSheet.Range("C5:D24").Value
'...and another range on another sheet added to an array
vaNames = Sheets("Name_Matrix").Range("B5:C68").Value

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(vaNames)
        If vaNames(i, 1) = "P" Then .Item(vaNames(i, 2)) = 1    '(just fill the dictionary)
    Next i

    For Each x In vaRoster
        If Len(x) > 0 Then
            If .Exists(x) Then    'If value in array matches value in 2nd array
                vaCount = vaCount + 1: ReDim Preserve vaPNames(1 To vaCount)    '(3rd array is one-dimensional array)
                vaPNames(vaCount) = x    '...I want to add that value to a 3rd array.
            End If
        End If
    Next x
End With

MsgBox vaCount
'or
MsgBox UBound(vaPNames)
End Sub

[Foreverlearning]

What I was trying to do is really learn how to add a range to an array add another range of names to an array and see which of the first array matched the criteria of the second array and add that to a 3rd array and count the number in that 3rd array.

Thank you nilem.

You have given me what I wanted and I really have to study these to fully understand otherwise I am just parroting you.

By the way can you add non contiguous range to the 1st array as in

'I have a range on one sheet which I have added to an array
vaRoster = ActiveSheet.Range("C5:D24").Value 'this range here like .Range("C5:D14","H5:I15")
'...and another range on another sheet added to an array
vaNames = Sheets("Name_Matrix").Range("B5:C68").Value

I can't seem to get this to work

Many thanks
Lionel

[nilem]

You have small ranges, so you can use Cells:

Sub Count_Names2()
Dim vaNames() As Variant, vaPNames() As Variant
Dim i As Long, vaCount As Long, r As Range
vaNames = Sheets("Name_Matrix").Range("B5:C68").Value

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(vaNames)
        If vaNames(i, 1) = "P" Then .Item(vaNames(i, 2)) = 1    '(just fill the dictionary)
    Next i

    For Each r In Range("C5:D14,H5:I15").Cells
        If Len(r.Value) > 0 Then
            If .Exists(r.Value) Then    'If value in array matches value in 2nd array
                vaCount = vaCount + 1: ReDim Preserve vaPNames(1 To vaCount)    '(3rd array is one-dimensional array)
                vaPNames(vaCount) = r.Value    '...I want to add that value to a 3rd array.
            End If
        End If
    Next r
End With
MsgBox UBound(vaPNames)
End Sub

If you want to use arrays, it will look something like this (just for testing):

Sub test()
Dim x, y, a, ResultArr(), i&, j&, k&  'j& and j As Long are same
x = Range("C5:D14").Value: y = Range("H5:I15").Value
ReDim ResultArr(1 To UBound(x) + UBound(y))    'UBound(x, 1) and UBound(x) are same

For Each a In x
    If Len(a) > 0 Then k = k + 1: ResultArr(k) = a
Next a

For i = 1 To UBound(y) 'or For Each a In y - in this case are same
    For j = 1 To UBound(y, 2)
        If Not IsEmpty(y(i, j)) Then k = k + 1: ResultArr(k) = y(i, j)
    Next j
Next i
MsgBox "UBound(ResultArr) = " & UBound(ResultArr)
ReDim Preserve ResultArr(1 To k)
MsgBox "and now UBound(ResultArr) = " & UBound(ResultArr)
MsgBox "the 2nd element is """ & ResultArr(2) & """"

'and maybe:
'Me.lstPNames.List = ResultArr
End Sub

[Foreverlearning]

Hi nilem,

You are marvelous mate...

Is there any speed advantage over the cells and the array types.

I can extend the cells version ok, but the array version looks more complicated.
I have extended the cells version by quite a few not contiguous ranges so the array version is not that simple to extend.

Your thoughts

Lionel.

[nilem]

Arrays are processed faster, but the filling of array from cells on a worksheet (such as For each cel ... arr(i)=cel.Value) is a slow operation. I think the first code of the post #5 would be most suitable for you.

[Foreverlearning]

Thank you mate so are so very helpful.