VBA Find instance of text in cell from array values, return array's 2nd dimension value
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?
Sub bank_name_lookup()
Dim wb As Workbook
Dim banks, accounts As Worksheet
Dim alias As Variant
Dim i, j, y, x As Long
Set wb = ThisWorkbook
Set banks = wb.Sheets("banks")
Set accounts = wb.Sheets("accounts")
With banks
x = .Range("a" & Rows.Count).End(xlUp).Row
alias = Range(.Cells(2, 1), .Cells(x, 2)).Value
End With
With accounts
j = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To j
If Cells(i, 3).Value = "Bank" Then
For y = 1 To UBound(alias, 1)
If InStr(1, Cells(i, 2).Value, alias(y, 1) & " ", 1) > 0 Then
Cells(i, 4).Value = alias(y, 2)
End If
Next y
End If
Next i
End With
End Sub
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 » ! ◄ ◄
Re: VBA Find instance of text in cell from array values, return array's 2nd dimension valu
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.
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 » ! ◄ ◄
Re: VBA Find instance of text in cell from array values, return array's 2nd dimension valu
& 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.
Bookmarks