View Full Version : collection / array help
malleshg24
01-04-2018, 09:18 PM
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
01-05-2018, 02:40 AM
http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.0
collection, array ya dictionary...
get the result through v lookup
None of the above with VLookUp
In Code, use Arrays and Loops
malleshg24
01-05-2018, 10:11 PM
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
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
01-06-2018, 11:44 AM
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
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.