VBA x 2 loop code performance

[johnbird1988]

Hello,

I have the bit of code below that work fine but it is very very slow. This is due to there being two loops within the main code and a Levenshtein function also imbedded.

I was just wondering if anyone can help advise on possibly speeding it up. The code basically does a Fuzzy lookup to find a percentage match on another value. I can have a list of 100 values I want to look into a reference data for to see if there is anything similar. This reference data is currently over 10k rows.


My Code

Sub test()
Dim nrow, nValue, L3 As Long
Dim c, n As Range
Dim nString As String
Dim ThisRng As Variant

ThisRng = Sheet13.Range("H2:H10152") 'Application.InputBox("Select a range", "Get Range", Type:=8)

Application.ScreenUpdating = False

nrow = Sheet13.Range("A1").End(xlDown).Row

Sheet13.Range("D1").Value = Now()


For Each c In Sheet13.Range("A2:A" & nrow)

nValue = 0

For i = UBound(ThisRng, 1) To LBound(ThisRng, 1) Step -1

L3 = Levenshtein3(c.Value, ThisRng(i, 1))

If L3 > nValue Then

nString = L3 & "%" & ThisRng(i, 1)
nValue = L3

ElseIf L3 = nValue Then

nString = nString & Chr(10) & L3 & "%" & ThisRng(i, 1)
nValue = L3

Else

End If

Next

c.Offset(0, 1).Value = nString
Next c

Sheet13.Range("E1").Value = Now()

Application.ScreenUpdating = True

End Sub

Any help in its performance would be very help full.

Cheers,


John

 

[RickXL]

Hi,

Could you define "very very slow", please?

I picked a Levenshtein algorithm at random from the internet and 11,000 words and it ran in under 5 seconds on my, not very new, PC.

What sort of times are you getting? What sort of times are you wanting?

Do you know if the Levenshtein algorithm is taking the time or is it the loops in the main program?