need help because vba code is running slow

[roykana]

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

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

 

[offthelip]

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

This loop is going to be slow because is accesses the worksheet multiple times in a loop. change this to using an array.

 

[roykana]

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

This loop is going to be slow because is accesses the worksheet multiple times in a loop. change this to using an array.

dear mr. offthelip
what is the code solution? Please help me.
Thanks
roykana

 

[Fluff]

Are you just trying to return one value from Source, or multiple values?

 

[roykana]

Are you just trying to return one value from Source, or multiple values?

I use for multiple values

 

[Fluff]

How many?

 

[roykana]

How many?

dear mr. fluff,
if there are 100000 row records. do you mean the number of columns from the source?
thanks
roykana

 

[Fluff]

I mean how many values per row are you trying to return?

 

[roykana]

I mean how many values per row are you trying to return?

from the data source there are 100000 rows

 

[Fluff]

How many columns of data are you trying to return per row, not how many rows.