Macro Theory Question

Peteor

Board Regular
Team,

I routinely find myself using For Each loops with embedded If statements to quickly parse down Excel data, and place various values on other sheets. Although not optimal, for smaller Excel sheets this has always quickly worked for me. I am being handed a project based on my Excel knowledge where I have to parse through 10+ 1,000,000 Row Worksheets. For a project this size, I do believe my routine method will be too clunky/slow. Does anyone have any recommendations for a better high volume method? One shortcoming of this method I have found previously is the number of times it loops through Cel & Cel2, however I have never figured out how to break the extra loop. Below is an example of what I would typically use.

VBA Code:
``````Sub Peteor()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim Cel As Range
Dim Rng As Range
Dim Cel2 As Range
Dim Rng2 As Range

Set Rng = Worksheets("Sheet1").Range("B2:B10000)
Set Rng2 = Worksheets("Sheet2").Range("C2:C10000)

For Each Cel In Rng
For Each Cel2 In Rng2

If Cel.Value = Cel2.Value Then

Worksheets("Sheet3").Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = Cel.Offset(0, 4).Value

Else
End If

Next Cel2
Next Cel

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub``````

Last edited by a moderator:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Marc L

Well-known Member
Hi,​
my first advice for huge data is to never use Excel as it is very not as fast as any database software !​
Looping cell by cell is the slowest way !​
So often an easy & faster way is to just use Excel basics like filter, formula, whatever …​
You can loop on arrays variables rather than on cell, this way could be the fastest​
but here the far slowest operation is your Sheet3 allocating cell by cell …​
An attachment with the before state and the expected result may help to find out a better way.​

diddi

Well-known Member
if you are trying to get matches where cel = cel2 then sorting both arrays will help heaps because it provides an exit point in your search. it cam also provide an entry point.
another method is to index the array which cuts search time. even just reading the range as an array will make a speed improvement

mumps

Well-known Member
Try this approach:
VBA Code:
``````Sub Peteor()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim v1 As Variant, v2 As Variant, i As Long
v1 = Sheets("Sheet1").Range("B2:B10").Resize(, 5).Value
v2 = Sheets("Sheet2").Range("C2:C10").Value
With CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v2, 1)
If Not .Exists(v2(i, 1)) Then
End If
Next i
For i = 1 To UBound(v1, 1)
If .Exists(v1(i, 1)) Then
Sheets("Sheet3").Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = v1(i, 5)
End If
Next i
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub``````

Fluff

MrExcel MVP, Moderator

For something like that I'd use arrays & a dictionary, like
VBA Code:
``````Sub Peteor()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim Ary1 As Variant, Ary2 As Variant, Nary As Variant
Dim i As Long

Ary1 = Worksheets("Sheet1").Range("B2:F10000").Value2
Ary2 = Worksheets("Sheet2").Range("C2:C10000").Value2
ReDim Nary(1 To UBound(Ary1), 1 To 1)

With CreateObject("scripting.dictionary")
For i = 1 To UBound(Ary2)
.Item(Ary2(i, 1)) = Empty
Next i
For i = 1 To UBound(Ary1)
If .Exists(Ary1(i, 1)) Then
nr = nr + 1
Nary(nr, 1) = Ary1(R, 4)
End If
Next i
End With
Worksheets("Sheet3").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(nr).Value = Nary

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub``````

Peteor

Board Regular
For something like that I'd use arrays & a dictionary, like
VBA Code:
``````Sub Peteor()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim Ary1 As Variant, Ary2 As Variant, Nary As Variant
Dim i As Long

Ary1 = Worksheets("Sheet1").Range("B2:F10000").Value2
Ary2 = Worksheets("Sheet2").Range("C2:C10000").Value2
ReDim Nary(1 To UBound(Ary1), 1 To 1)

With CreateObject("scripting.dictionary")
For i = 1 To UBound(Ary2)
.Item(Ary2(i, 1)) = Empty
Next i
For i = 1 To UBound(Ary1)
If .Exists(Ary1(i, 1)) Then
nr = nr + 1
Nary(nr, 1) = Ary1(R, 4)
End If
Next i
End With
Worksheets("Sheet3").Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Resize(nr).Value = Nary

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub``````
Thank you Fluff, and everyone for your replies! This one is what I will be looking into for this project. You guys are always super helpful! Thanks again!

Fluff

MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
4
Views
219
Replies
4
Views
145
Replies
12
Views
432
Replies
3
Views
85
Replies
0
Views
104

1,141,707
Messages
5,707,983
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back