35,000+ row loop

Question

I'm a VBA novice but have had some help with some code I need from a friend.

Essentially, there's 35,000+ rows of data. We are looking for cells that match a one of four different criteria in column H (from memory). If it meets one of these four criteria it slightly changes the data in the cell and then searches for a matching entry within the worksheet.

The problem is that every time it has a hit it's searching through 35,000+ rows of data and there isn't always the matching entry for it.

This is taking a substantial amount of time (over 20 hours in total for the entire sheet!).

Is there anyway to speed up this process? For example maybe mimicking the CTRL-F function instead of looping through all of the rows...?

Any help would be greatly appreciated.

Dim VAR_AMOUNT As Double
Dim VAR_STRING As String
Dim VAR_EDITED_STRING As String
Dim VAR_SOURCE As String
Dim VAR_STA_ROW As Long
Dim VAR_MAT_ROW As Long
Dim VAR_END_ROW As Long
Dim VAR_MATCHES As Integer
Dim VAR_ROW_SAV As Long 

VAR_END_ROW = VarRunToBox
VAR_STA_ROW = VarRunFromBox
VAR_MAT_ROW = 1

Tag0:
Range(Cells(VAR_STA_ROW, 8), Cells(VAR_STA_ROW, 8)).Select
VAR_STRING = ActiveCell.Value
VAR_EDITED_STRING = VAR_STRING
Range(Cells(VAR_STA_ROW, 7), Cells(VAR_STA_ROW, 7)).Select
VAR_SOURCE = ActiveCell.Value
Range(Cells(VAR_STA_ROW, 5), Cells(VAR_STA_ROW, 5)).Select
VAR_AMOUNT = ActiveCell.Value
If VAR_AMOUNT < 0 Then VAR_AMOUNT = (VAR_AMOUNT * -1)
If VAR_SOURCE <> "SDE" Then GoTo Tag1

VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 1)
If IsNumeric(VAR_EDITED_STRING) = False Then GoTo Tag1
VAR_EDITED_STRING = VAR_STRING


VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 14)
If IsNumeric(VAR_EDITED_STRING) = True Then GoTo Tag1
VAR_EDITED_STRING = VAR_STRING


VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 13)
If IsNumeric(VAR_EDITED_STRING) = True Then
    VAR_EDITED_STRING = VAR_STRING
    VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 13)
    VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 10) & "0" &      Right(VAR_EDITED_STRING, 3)
    If VAR_AMOUNT >= 15 Then

        Do

VAR_MAT_ROW = VAR_MAT_ROW + 1
Range(Cells(VAR_MAT_ROW, 9), Cells(VAR_MAT_ROW, 9)).Select
            If IsEmpty(ActiveCell.Value) Then GoTo TAG2A
            If ActiveCell.Value = VAR_EDITED_STRING Then
                    VAR_ROW_SAV = ActiveCell.Row
                    VAR_MATCHES = VAR_MATCHES + 1
                    Range(Cells(VAR_STA_ROW, 17 + VAR_MATCHES), Cells(VAR_STA_ROW, 17 + VAR_MATCHES)).Select
                    ActiveCell.Value = VAR_ROW_SAV

            Else
            End If
TAG2A:
            If VAR_MAT_ROW = VAR_END_ROW Then GoTo Tag1


        Loop
    Else

        Do
     VAR_MAT_ROW = VAR_MAT_ROW + 1
            Range(Cells(VAR_MAT_ROW, 8), Cells(VAR_MAT_ROW, 8)).Select
            If IsEmpty(ActiveCell.Value) Then GoTo TAG2B
            If ActiveCell.Value = VAR_EDITED_STRING Then
                    VAR_ROW_SAV = ActiveCell.Row
                    VAR_MATCHES = VAR_MATCHES + 1
                    Range(Cells(VAR_STA_ROW, 17 + VAR_MATCHES), Cells(VAR_STA_ROW, 17 + VAR_MATCHES)).Select
                    ActiveCell.Value = VAR_ROW_SAV

            Else
            End If
            Range(Cells(VAR_MAT_ROW, 9), Cells(VAR_MAT_ROW, 9)).Select
            If IsEmpty(ActiveCell.Value) Then GoTo TAG2B
            If ActiveCell.Value = VAR_EDITED_STRING Then 
                    VAR_ROW_SAV = ActiveCell.Row
                    VAR_MATCHES = VAR_MATCHES + 1
                    Range(Cells(VAR_STA_ROW, 17 + VAR_MATCHES), Cells(VAR_STA_ROW, 17 + VAR_MATCHES)).Select
                    ActiveCell.Value = VAR_ROW_SAV

            Else
            End If
TAG2B:
            If VAR_MAT_ROW = VAR_END_ROW Then GoTo Tag1

        Loop

    End If
Else
    VAR_EDITED_STRING = VAR_STRING
    If VAR_AMOUNT >= 15 Then
        VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 7)
        If IsNumeric(VAR_EDITED_STRING) = True Then
            VAR_EDITED_STRING = VAR_STRING
            VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 7)
            Do

            VAR_MAT_ROW = VAR_MAT_ROW + 1
            Range(Cells(VAR_MAT_ROW, 8), Cells(VAR_MAT_ROW, 8)).Select
            If IsEmpty(ActiveCell.Value) Then GoTo TAG2C
            If ActiveCell.Value = VAR_EDITED_STRING Then
                    VAR_ROW_SAV = ActiveCell.Row
                    VAR_MATCHES = VAR_MATCHES + 1
                    Range(Cells(VAR_STA_ROW, 17 + VAR_MATCHES), Cells(VAR_STA_ROW, 17 + VAR_MATCHES)).Select
                    ActiveCell.Value = VAR_ROW_SAV

            Else
            End If
            Range(Cells(VAR_MAT_ROW, 9), Cells(VAR_MAT_ROW, 9)).Select
            If IsEmpty(ActiveCell.Value) Then GoTo TAG2C
            If ActiveCell.Value = VAR_EDITED_STRING Then
                    VAR_ROW_SAV = ActiveCell.Row
                    VAR_MATCHES = VAR_MATCHES + 1
                    Range(Cells(VAR_STA_ROW, 17 + VAR_MATCHES),  Cells(VAR_STA_ROW, 17 + VAR_MATCHES)).Select
                    ActiveCell.Value = VAR_ROW_SAV

            Else
            End If
TAG2C:
            If VAR_MAT_ROW = VAR_END_ROW Then GoTo Tag1
            Loop
        Else
            GoTo Tag1
        End If
    Else
        GoTo Tag1
    End If

End If

Tag1:
VAR_STA_ROW = VAR_STA_ROW + 1
VAR_MATCHES = 0
VAR_MAT_ROW = 1
If VAR_STA_ROW = VAR_END_ROW Then GoTo Tag3

GoTo Tag0

Tag3: 

Endsubfail:
End Sub

Also look into using variant arrays instead of looping ranges. — Scott Craner, Oct 22 '18 at 17:34
You have the right idea. Replace you loop with a search function. Look into the Range.Find method. You will then need to code for the the possibility of something being found, and the possibility of nothing being found. — urdearboy, Oct 22 '18 at 17:35

score 0 · Answer 1 · answered Oct 22 '18 at 20:27

There are a few massive issues with this code which make it:

Not easy to maintain.
Slow.

It is unlikely that using range.find() will really fix your issue as your problem is not VBA, but how you are structuring your algorithm.

There are many answers already about optimising code, but here's one of mine that you might find helpful.

Main suggestions for this post:

Don't loop over cells. Use arrays!

Do not loop over each cell in a table like this! This is something that I have seen so many people do and it is the thing which makes most VBA code extremely slow! This:

dim var as Variant
for i = 1 to 10000
    var = cells(i,1).value
next

Will always take a lot longer than this:

Dim var, arr as variant
arr = range(cells(1,1),cells(1,10000)).value
for i = 1 to 10000
   var = arr(i,1)
next

You don't need a variable to have a condition!

A variable will not be altered if it is passed to a function (in most situations). This:

VAR_EDITED_STRING = Left(VAR_EDITED_STRING, 1)
If IsNumeric(VAR_EDITED_STRING) = False Then GoTo Tag1
VAR_EDITED_STRING = VAR_STRING

is unnecessarily complicated! Use this:

If Not IsNumeric(Left(VAR_STRING, 1)) Then GoTo Tag1

Don't use Labels. Use statements

I know that labels are quite handy but they are messy and really make the actual fact that a jump is occuring invisible (at least if you are indenting your code) change:

If Not IsNumeric(Left(VAR_STRING, 1)) Then GoTo Tag1

to this:

If IsNumeric(Left(VAR_STRING, 1)) Then
    '...other code...
end if

This will vastly improve readibility of the code (it won't affect performance though)

That should do for now.

score 0 · Answer 2 · answered May 12 '20 at 03:13

It doesn't look like you are a novice. Now, I only glanced at this one, but you know that the real difference between int and long is the size of its memory space and therefore the size of the number it can hold...right

An Integer is 16 bits and can represent a value between -32,768 and 32,767

A Long is 32 bits and can represent -2,147,483,648 to 2,147,483,647

I just saw the 35k row thing and made an assumption. Can you make sure everything is long and re-run the process? Finally, take a look at Excel's 'SpecialCells' method. The last time I used this was at least 10 years ago, but as I recall, it was like some kind of loop that performed as an array. So, you have the control of a loop process running at the speed of an array process.

35,000+ row loop

2 Answers2

Main suggestions for this post:

Don't loop over cells. Use arrays!

You don't need a variable to have a condition!

Don't use Labels. Use statements