Matching loop causing "not respond" to excel program

Kabasauls

New Member
Joined
Jul 12, 2012
Messages
24
Hello,

Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed.

ABCDEFGHIJKL
TypeNbrPost
Auto Rev

<tbody>
</tbody>
Nbr

<tbody>
</tbody>
Nbr

<tbody>
</tbody>
Date

<tbody>
</tbody>
Create User

<tbody>
</tbody>
Description

<tbody>
</tbody>
Balance

<tbody>
</tbody>
Amount

<tbody>
</tbody>
Amount

<tbody>
</tbody>
APAD6546565464XY4-26XXXsdfsdfsasdfd
0

<tbody>
</tbody>
43053

<tbody>
</tbody>
APAD6548121548XY4-12XXXsdfsdfadfas 0 54152
APVO4648154123XY5-3XXXasdfasdadsfa43053 0

<tbody>
</tbody>

Logic: When the non-zero amount on column K matches the one in column L, the macro colors the respective rows. The macro skips the already colored cells and only compares the non-zero values. Once the macro attempts to match all the values, a filter is used to show only the non-color cells. These cells are the unmatched ones.

Issue: The macro worked with the original data of 188 rows. Once a bigger database was attempted, about 15000 rows, excel does not respond after a few seconds and crashes. The first time, there was a message that pointed out to this line of my code in my Scan sub:

Code:
If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then
Macro:

Code:
Private Sub CommandButton1_Click()


screenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality for efficiency
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting


Call clear_formats
Call Scan
Call filter


'Resets settings
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting


End Sub


Private Sub Scan()Dim Rows1 As Integer

Dim Rows2 As Integer
Dim lastRow As Integer
Dim i As Integer
Dim m As Integer
i = 1
m = 1
 
'Find number of rows with data
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
'Scan first column
For i = 2 To lastRow
    For m = 2 To lastRow
    If Cells(i, 11).Value <> 0 And Cells(m, 12).Value <> 0 Then
        'Check for a color
            If (Cells(i, 11).Interior.ColorIndex = xlNone) And (Cells(m, 12).Interior.ColorIndex = xlNone) Then
            'looks for a match
                If Cells(i, 11).Value = Cells(m, 12).Value Then
                    Rows(i).Interior.ColorIndex = 42
                    Rows(m).Interior.ColorIndex = 42
                End If
            End If
    End If
    Next m
Next i

End Sub

Private Sub filter()

'filter by colorindex = 42 in column 11
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$N$100288").AutoFilter Field:=11, Operator:= _
        xlFilterNoFill


End Sub


Private Sub clear_formats()
'Reset filter
Worksheets("Test").AutoFilterMode = False


'Reset formats of whole page


Cells.Select
Selection.ClearFormats




Range("A1").Select


End Sub


Private Sub CommandButton2_Click()


Application.ScreenUpdating = False


Call clear_formats


Application.ScreenUpdating = True


End Sub


Question:
Why does the macro work on a smaller scale but crashes when I use about 15000 rows? Can it be the loop is too slow and process intensive?
 

Kabasauls

New Member
Joined
Jul 12, 2012
Messages
24
Ignore this post. I already figured it out. I do not know how to erase this from the forum.
 

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top