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

 

[roykana]

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

There are 3 columns

 

[Fluff]

Ok how about

Sub Demo03()
Dim Rng As Range, Ds As Range, n As Long, Dic As Object, Source As Variant
Dim Ary 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")
    Ary = .Range("c2", .Range("c" & Rows.Count).End(xlUp)).Value2
    ReDim Nary(1 To UBound(Ary), 1 To 3)
    For n = 1 To UBound(Ary)
        If Dic.Exists(Ary(n, 1)) Then
            Nary(n, 1) = Source(Dic(Ary(n, 1)), 1)
            Nary(n, 2) = Source(Dic(Ary(n, 1)), 3)
        End If
    Next n
    Range("D2").Resize(UBound(Nary), 3).Value = Nary
End With


Application.ScreenUpdating = True

Debug.Print "Demo-03????:" & amp; amp; endTime - startTime
Debug.Print "It's done in: " & Timer - t & " seconds"
End Sub

 

[roykana]

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

i have an error. in the line of code below

Nary(n, 2) = Source(Dic(Ary(n, 1)), 3)

 

[roykana]

i have an error. in the line of code below

Nary(n, 2) = Source(Dic(Ary(n, 1)), 3)

my code below is wrong

  Ds.Offset(, 1) = Source(Dic(Ds.Value), 1)

this is true

Ds.Offset(, 1) = Source(Dic(Ds.Value), 2)

 

[Fluff]

Missed the fact that your source array only has two columns, try

Source = .Range("h1").CurrentRegion.Resize(, 4)

 

[roykana]

Missed the fact that your source array only has two columns, try

Source = .Range("h1").CurrentRegion.Resize(, 4)

ok. I've coded from you below, I show a screenshot of the results of the time.
Can the time result be reduced to lower again from the current result?

 

[Fluff]

What's wrong with 10 seconds?

 

[roykana]

What's wrong with 10 seconds?

can it be under 10 seconds?

 

[Fluff]

For 100,000 rows of data, probably not.

 

[roykana]

For 100,000 rows of data, probably not.

Sorry for the late reply, thanks for the help. you are a master