2 array compare using vba function

[Masha92]

Hello everyone

I need your help with my code. I get runtime error #9 (subscript out of range). I have a function to check if a value is in an array or not. I can do this with other ways but I need to understand what is wrong with my writing. I hope you can help me answer that


'setting up arrays
Dim New_Cons(), A(), B(), Old_Cons(), Memberships() As Variant, I As Integer


ReDim Preserve Old_Cons(0 To sht2.Cells(Cells.Rows.Count, "O").End(xlUp).Row)
ReDim Preserve New_Cons(0 To sht1.Cells(Cells.Rows.Count, "B").End(xlUp).Row - 11)
Old_Cons() = sht2.Range("O2:O" & sht2.Cells(Cells.Rows.Count, "o").End(xlUp).Row).Value
New_Cons() = sht1.Range("b11:B" & sht1.Cells(Cells.Rows.Count, "B").End(xlUp).Row).Value


'comparing arrays


For I = 0 To UBound(Old_Cons)


If IsInArray(Old_Cons(I), New_Cons) = False Then
ReDim Preserve Memberships(0 To K)
Memberships(K) = Old_Cons(I) & "/" & "Drop"
K = K + 1
End If


Next I

My function is as below:

Public Function IsInArray(A As Variant, B As Variant) As Boolean
Dim I As Integer, X As Integer
IsInArray = True




On Error GoTo NotInArray


X = WorksheetFunction.Match(A, B, 0)


If Not IsNumeric(X) Then
IsInArray = False
End If




NotInArray:




End Function

 

[Marcelo Branco]

Thoughts
The array created by passing a range has two dimensions with the first index = 1, not 0.

So try
ReDim Old_Cons (1 To sht2.Cells (Cells.Rows.Count, "O"). End (xlUp) .Row)

And when referring to a vertical array element use
Old_Cons (I, 1)



Hope this helps

M.

 

[Marcelo Branco]

By the way, ReDim is not needed when passing a range to a Variant.

See if this example clarifies things

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Names​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Mary​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Anthony​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub aTest()
    Dim MyArray As Variant, i As Long
    
    MyArray = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
    
    For i = 1 To UBound(MyArray)
        Debug.Print MyArray(i, 1)
    Next i
    
End Sub

M.

 

[Masha92]

Thank you so much marcelo. Indeed, this was the issue!
Though, i do not understand why it was declared as 2 dimensions? is this the default when you pass a range into an array?

Thanks again!!

By the way, ReDim is not needed when passing a range to a Variant.

See if this example clarifies things

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Names​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Mary​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Anthony​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub aTest()
    Dim MyArray As Variant, i As Long
    
    MyArray = Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
    
    For i = 1 To UBound(MyArray)
        Debug.Print MyArray(i, 1)
    Next i
    
End Sub

M.

 

[Marcelo Branco]

You're welcome. Glad to help

M.
ps: Yes, when passing a range to a Variant the array has two dimensions even when the range has only one column/row.

 

[Masha92]

Much appreciated. finished the project!