collection / array help

[malleshg24]

Hi Team,


I am trying to learn collection , array, dictionary. In below example can someone
suggest which option to use to get the result. through v lookup i can get the result.
Plz suggest answer in collection , array ya dictionary. Thanks.

Colum A id needs to compare with column D id, and put the result in Column B.
if found as found else not found. I have huge data , I can later modify the code as per my requirement. Thanks.in advance.

A	B		D
Trade ID	Status		TradeID
659427	Not Found		659287
659332	Not Found		659537
659410	Found		659614
659302	Not Found		659899
659492	Found		5932781
6599999	Not Found		453125
659463	Found		659463
			659410
			659492
			658281
			658257
			658222
			658278
			658214
			658253
			658243
			658295

Regards,
Mallesh

[mancubus]

http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0

[SamT]

collection, array ya dictionary...
get the result through v lookup

None of the above with VLookUp

In Code, use Arrays and Loops

[malleshg24]

Hi Team,

Thanks for the information, I tried from my end, but not successful in comparing the array
and putting the matched result. I reached till adding values in array
Below are my code, Plz assist me in looping, Thanks again for your precious time!

Option Explicit

Sub Testing()
Dim myarray1() As Variant, myarray2() As Variant
Dim Lr1 As Long
Dim Lr2 As Long
Dim index1 As Integer, index2 As Integer
Dim row1 As Integer, row2 As Integer, i As Integer, j As Integer, 
row1 = 1
row2 = 1
'First Array to store data in column 1..................
Lr1 = Range("a100").End(xlUp).Row - 1
    ReDim myarray1(Lr1)
For index1 = LBound(myarray1) To UBound(myarray1)
    myarray1(index1) = Cells(row1, 1).Value
      row1 = row1 + 1
Next

'SECOND ARRAY to store data in colulmn 4.........................
Lr2 = Range("D100").End(xlUp).Row - 1
ReDim myarray2(Lr2)
For index2 = LBound(myarray2) To UBound(myarray2)
        myarray2(index2) = Cells(row2, 4).Value
         row2 = row2 + 1
Next

'Comparision of array
Dim index3 As Long, constantindex As Long
constantindex = 0
For i = 1 To UBound(myarray1)
    If myarray1(constantindex) = myarray2(index3) Then
For j = LBound(myarray2) To UBound(myarray2)

'Plz assist here....
    
Next j
Next i

End Sub

[SamT]

Sub LabelDuplicates()
'You should declare all Variables here

Lr1 = Cells(Rows.Count, "A").End(xlUp).Row
Lr2 = Cells(Rows.Count, "D").End(xlUp).Row

ReDim MyArrayB(Lr1 - 1)

MyArray1 = Range(Range("A2"), Cells(Lr1, "A"))
MyArray2 = Range(Range("D2"), Cells(Lr2, "D"))

For i = 1 To Lr1 - 1 'Ranged Arrays' indices start at 1
MyArrayB(i) = "Not Found"
For j = 1 To Lr2 - 1
If MyArray2(j) = MyArray1(i) Then
MyArrayB(i) = "Found"
GoTo NextI
End If
Next
NextI:
Next

Range("B2").Resize(Lr1 - 1, 1) = MyArrayB
End Sub

[malleshg24]

1Hi Sam,

Thanks for your quick reply,

I have declared array as you mentioned.

But still I am getting Subscript out of range error message at line If MyArray2(j) = MyArray1(i) Then

I am unable to get the reason why its throwing a error. Plz assist.Sub LabelDuplicates()
'You should declare all Variables here

Dim myarrayB() As Variant
Dim myarray1 As Variant
Dim myarray2 As Variant

Dim i As Integer
Dim j As Integer

Dim Lr1 As Long
Dim lr2 As Long

Lr1 = Cells(Rows.Count, "A").End(xlUp).Row
Lr2 = Cells(Rows.Count, "D").End(xlUp).Row

ReDim MyArrayB(Lr1 - 1)

MyArray1 = Range(Range("A2"), Cells(Lr1, "A"))
MyArray2 = Range(Range("D2"), Cells(Lr2, "D"))

For i = 1 To Lr1 - 1 'Ranged Arrays' indices start at 1
MyArrayB(i) = "Not Found"
For j = 1 To Lr2 - 1
If MyArray2(j) = MyArray1(i) Then
MyArrayB(i) = "Found"
GoTo NextI
End If
Next
NextI:
Next

Range("B2").Resize(Lr1 - 1, 1) = MyArrayB
End Sub


Regards,
Mallesh

[SamT]

Hmmnnn?
Add these three lines and set a Watch on X

MyArray1 = Range(Range("A2"), Cells(Lr1, "A")) 
    MyArray2 = Range(Range("D2"), Cells(Lr2, "D")) 
     Dim X'<--
X = Lbound(MyArray1)'<--
X = Lbound(MyArray2)'<--
    For i = 1 To Lr1 - 1 'Ranged Arrays' indices start at 1

I'm till using Excel 2002. maybe things change.

There are other Logical errors in the code, which I hope you find without help. IMO, the best way to learn is to learn to fix errors.