LinkBack URL
About LinkBacks
Greetings all.
While the below works, my data range(s) will exceed a 100k rows. I suspect this would be better achieved comparing two dictionaries objects with both ranges?
Please Login or Register to view this content.
Greetings all.
While the below works, my data range(s) will exceed a 100k rows. I suspect this would be better achieved comparing two dictionaries objects with both ranges?
Please Login or Register to view this content.
Hello, try this !
According to your attachment a VBA demonstration for starters to paste only to the Sheet2 (accounts) worksheet module :
PHP Code:Sub Demo1()
Dim V, R&, oCol As New Collection, S$()
V = Sheet1.[A1].CurrentRegion
For R = 2 To UBound(V): oCol.Add V(R, 2), V(R, 1): Next
V = [A1].CurrentRegion.Columns("B:C")
Application.ScreenUpdating = False
For R = 2 To UBound(V)
If V(R, 2) = "Bank" Then
S = Split(V(R, 1))
Cells(R, 4) = oCol(S(UBound(S) - 1 + (Asc(S(UBound(S) - 1)) > 90)))
End If
Next
Application.ScreenUpdating = True
Set oCol = Nothing
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Thanks Marc. Couple of questions as the above for my understanding, as it is advanced for a me.
Why does the code require to be in the sheet module?
What is happening with & and $ in the R and S variables?
I ran some performance tests on 800k records (by multiplying the available data).
There are 6 procedures in the attached file.
The first three enter the data cell by cell, the next three fill the array and finally fill the entire range. This is obviously more efficient than the first method.
But I also compared the performance of using Collection and Dictionary. Dictionary is faster than Collection, but this test did not confirm that at all. It also turned out that using a loop to search for bank names also performed very well. It is a little slower than using Collection or Dictionary, but the fraction of a second difference on 800K records may be negligible.
As we run all the tests, the worksheet shows approximate execution times for each procedure. More realistic times are shown in the Immediate window.
I leave the choice of method to OP.
Marc L,Clever, very clever.Please Login or Register to view this content.
Artik
Re: VBA Find instance of text in cell from array values, return array's 2nd dimension valu
• In addition to Artik's post, using a Dictionary is not always the fastest / more efficient method
as with huge data - ~ 500K unique items & more - it becomes slower than using a Collection …
As yet demonstrated in this forum in some cases an optimized loop on array could be the best option, faster than using a Dictionary …
• Thanks Artik for your kind appreciation !
• Answers to DHHM post #3 :
My VBA basics demonstration is just designed to be in the Sheet2 worksheet module
as there is no reference to this worksheet, in case this worksheet is not active.
& is just an old school coding to declare a Long variable as you can see in VBA help
or just during the execution checking the variables within the VBE Locals window …
Another VBA basics demonstration which can be located in a general / standard module like Module1 :
PHP Code:Sub Demo2()
Dim A, B, V, R&, L&
Application.ScreenUpdating = False
With Sheet1.[A1].CurrentRegion.Rows
A = .Parent.Evaluate("A2:A" & .Count & "&"" """)
B = .Range("B2:B" & .Count)
End With
With Sheet2.[A1].CurrentRegion
V = .Columns("B:C")
For R = 2 To UBound(V)
If V(R, 2) = "Bank" Then
For L = 1 To UBound(A)
If InStr(1, V(R, 1), A(L, 1), 1) Then .Cells(R, 4) = B(L, 1): Exit For
Next
End If
Next
End With
Application.ScreenUpdating = True
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 06-07-2022 at 08:29 AM.
& is just an old school coding to declare a Long variable as you can see in VBA help
or just during the execution checking the variables within the VBE Locals window …
I wondered how it was doing that. Another thing I've learned today!
Late in the pm over here so I will spend the next few days looking at Artik's file, and your suggestion above and understanding how each does what they do.
Re: VBA Find instance of text in cell from array values, return array's 2nd dimension valu
As Demo2 is just an optimized version of your original code …
Thanks for the rep' !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks