VBA:: checking values from one array to another

Question

I am trying to write a VBA code that cross checks all of the values in one array with another (X with Y) and to copy the value into a new array if it is duplicated (FinalResults). So far I have the following code and would greatly appreciate some guidance on how to write it correctly.

Function lnArray(X as Variant, Y as Variant) As Variant
Dim counter1 As Integer
Dim data As Integer
Dim FinalResults() As Variant

For counter1 = 1 To Max
    For Each data In X
        If data.Value = Y.Value Then
            counter1 = counter1 + 1
            ReDim Preserve FinalResults(counter1)
            FinalResults(counter1) = data.Value
        End If
    Next data
Next counter1
End Function

Must the original arrays be preserved? What size do they have? — Dan
– Dan, Commented Jun 24, 2011 at 6:16
I feels like a million variations on this question been answered on SO already. Can you not find inspiration in any of them? Most questions are about retaining the unique values, while you want to do the opposite, namely retain the non-unique values. Any of the solutions linked to above can probably be adapted to do what you want by adding a well-placed Not somewhere. — Jean-François Corbett
– Jean-François Corbett, Commented Jun 24, 2011 at 6:52

Christoph Jüngling · Accepted Answer · 2011-07-04 17:35:51Z

2

First thing I saw when I looked over the code was, that Max is neither declared nor initialized. Even if I assume that Option Explicit is not set, this for-next loop runs from 1 to 0, which means no loops at all. I guess that's not what you intended?

Second is, your FinalResult array is declared locally in this function, hence no kind of result value can be returned to the calling function.

answered Jul 4, 2011 at 17:35

Christoph Jüngling

1,1008 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA:: checking values from one array to another

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related