VBA Nested For Loop Crashing Excel

Question

I am currently trying to create a list of all possible combinations of entries from two separate sheets, but whenever I try to run it, Excel crashes after about 20 seconds. Does anybody have any tips for how to make this more efficiently, or a way to make this work? Thanks!

Sub Create()
Dim dates, groups, current As Integer
Dim dateValue As Date
Dim groupValue As String
Dim cell As Long

Application.ScreenUpdating = False
Sheets(3).Cells.Clear
cell = 1

For dates = 1 To 730

    Sheets(1).Select
    dateValue = Cells(dates, 1).Value

    For groups = 1 To 155

        Application.StatusBar = dateValue & " " & groupValue

        Sheets(2).Select
        groupValue = Cells(groups, 1).Value

        Sheets(3).Select

        Cells(cell, 1) = dateValue
        Cells(cell, 2) = groupValue

        cell = cell + 1

    Next groups

Next dates

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

If an answer solved your problem you can click the check mark to help reward those who helped you :) — bmende
– bmende, Commented May 4, 2016 at 15:49

Alex K. · Accepted Answer · 2015-04-22 14:24:44Z

2

Remove the .Select calls.

groupValue = Sheets(2).Cells(groups, 1).Value

Is better than

Sheets(2).Select
groupValue = Cells(groups, 1).Value

.Select is slow and expensive and unnecessary.

Does the statusbar actually update? doing so 100k times is likewise a bottleneck; use a mod counter to update every nth iteration.

answered Apr 22, 2015 at 14:24

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DomSchwe Over a year ago

Removing all of the '.Select' worked perfectly! And thank you for pointing out the status bar issue, once I changed it to only reflect the date value then everything worked.

Gareth · Accepted Answer · 2015-04-22 14:32:16Z

2

Try this. You don't need to keep selecting the sheets as this will an an additional overhead to excel. Instead just reference the cell like so:

Sub Create()
Dim dates, groups, current As Integer
Dim dateValue As Date
Dim groupValue As String
Dim cell As Long

Application.ScreenUpdating = False
Sheets(3).Cells.Clear
cell = 1

For dates = 1 To 730

    dateValue = Sheets(1).Cells(dates, 1).Value

    For groups = 1 To 155

        Application.StatusBar = dateValue & " " & groupValue

        groupValue = Sheets(2).Cells(groups, 1).Value

        Sheets(3).Cells(cell, 1) = dateValue
        Sheets(3).Cells(cell, 2) = groupValue

        cell = cell + 1

    Next groups

Next dates

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

edited Apr 22, 2015 at 14:32

answered Apr 22, 2015 at 14:24

Gareth

5,2636 gold badges47 silver badges73 bronze badges

1 Comment

DomSchwe Over a year ago

Did this, except moved the status bar to only update to reflect the date, it worked great!

Collectives™ on Stack Overflow

VBA Nested For Loop Crashing Excel

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related