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.:help:help



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.