Dear all Master,
vba code runs slow for 100000 row records even though I'm using vba code with dictionaries and arrays. Please solve.
and also why the code run time result is different and I attached a screenshot of the time result.
thanks
roykana
vba code runs slow for 100000 row records even though I'm using vba code with dictionaries and arrays. Please solve.
and also why the code run time result is different and I attached a screenshot of the time result.
thanks
roykana
VBA Code:
Sub Demo03()
Dim Rng As Range, Ds As Range, n As Long, Dic As Object, Source As Variant
Dim startTime As Double
Dim endTime As Double
Dim t
t = Timer
endTime = Timer
'Dim startTime As Double
'Dim endTime As Double
Application.ScreenUpdating = False
With Sheets("data")
Source = .Range("h1").CurrentRegion.Resize(, 2)
End With
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For n = 2 To UBound(Source, 1)
Dic(Source(n, 1)) = n
Next
With Sheets("data")
Set Rng = .Range(.Range("c2"), .Range("c" & Rows.Count).End(xlUp))
For Each Ds In Rng
If Dic.Exists(Ds.Value) Then
Ds.Offset(, 1) = Source(Dic(Ds.Value), 1)
' Ds.Offset(, 3) = source(Dic(Ds.Value), 3)
End If
Next Ds
End With
Application.ScreenUpdating = True
Debug.Print "Demo-03????:" & amp; amp; endTime - startTime
Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub