Results 1 to 28 of 28

Optimize Excel Code - Too many FOR NEXT Loops

Threaded View

tdsg Optimize Excel Code - Too... 10-15-2021, 08:30 AM
Marc L Re: Optimize Excel Code - Too... 10-15-2021, 08:53 AM
tdsg Re: Optimize Excel Code - Too... 10-15-2021, 09:03 AM
tdsg Re: Optimize Excel Code - Too... 10-15-2021, 09:27 AM
6StringJazzer Re: Optimize Excel Code - Too... 10-15-2021, 10:37 AM
tdsg Re: Optimize Excel Code - Too... 10-15-2021, 10:48 AM
6StringJazzer Re: Optimize Excel Code - Too... 10-15-2021, 11:16 AM
tdsg Re: Optimize Excel Code - Too... 10-15-2021, 11:33 AM
tdsg Re: Optimize Excel Code - Too... 10-15-2021, 11:35 AM
tdsg Re: Optimize Excel Code - Too... 10-21-2021, 08:33 AM
jindon Re: Optimize Excel Code - Too... 10-21-2021, 11:10 AM
tdsg Re: Optimize Excel Code - Too... 10-21-2021, 12:31 PM
jindon Re: Optimize Excel Code - Too... 10-21-2021, 12:42 PM
tdsg Re: Optimize Excel Code - Too... 10-21-2021, 12:51 PM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 02:21 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 06:49 AM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 06:53 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 07:36 AM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 08:18 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 08:38 AM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 08:57 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 09:19 AM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 09:46 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 10:33 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 11:57 AM
tdsg Re: Optimize Excel Code - Too... 10-22-2021, 02:03 PM
jindon Re: Optimize Excel Code - Too... 10-22-2021, 09:52 PM
tdsg Re: Optimize Excel Code - Too... 10-25-2021, 07:28 AM
  1. #10
    Registered User
    Join Date
    06-10-2016
    Location
    United States
    MS-Off Ver
    Microsoft 365 Enterprise
    Posts
    63

    Re: Optimize Excel Code - Too many FOR NEXT Loops

    I found some really cool code that compares the two ranges more efficiently and turns red any cells interior colors that are different on the Main worksheet. Attached if interested - module password is still dmu123xls. The Compare button runs faster now than when i was using a zillion For Next Loops. I'm still figuring out how to show only the red rows or cells on the Summary worksheet. Presently, the Summarize button just copies the Main worksheet. Anyway, the really cool code, I think, is this.

    Dim name1 As String
    Dim name2 As String
    Dim range1 As Range
    Dim range2 As Range
    Dim cells1 As Collection
    Dim cells2 As Collection
    Dim cell1 As Range
    Dim cell2 As Range
    Dim key As String
    Dim no_match As Boolean
    
    Lastrow2 = ThisWorkbook.Worksheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
    
    name1 = "A3:AK"
        If Len(name1) = 0 Then Exit Sub
        Set range1 = ThisWorkbook.Worksheets("Main").Range(name1 & Lastrow2)
    
    name2 = "AM3:BW"
        If Len(name2) = 0 Then Exit Sub
        Set range2 = ThisWorkbook.Worksheets("Main").Range(name2 & Lastrow2)
    
        ' Make normal collections holding the cells.
        Set cells1 = New Collection
        For Each cell1 In range1.Cells
            key = cell1.Row - range1.Row & "," & cell1.Column - _
                range1.Column
            cells1.Add cell1, key
        Next cell1
    
        Set cells2 = New Collection
        For Each cell2 In range2.Cells
            key = cell2.Row - range2.Row & "," & cell2.Column - _
                range2.Column
            cells2.Add cell2, key
        Next cell2
    
        ' Examine the cells in the first collection.
        For Each cell1 In cells1
            On Error Resume Next
            Err.Clear
            key = cell1.Row - range1.Row & "," & cell1.Column - _
                range1.Column
            Set cell2 = cells2(key)
            If Err.Number <> 0 Then
                ' The second cell is missing.
                no_match = True
            ElseIf cell1.Text <> cell2.Text Then
                ' The cells don't match.
                no_match = True
            Else
                no_match = False
            End If
    
            ' If the cells don't match, color cell1.
            If no_match Then
                With cell1.Interior
                    .Color = RGB(255, 204, 204)
                    .Pattern = xlSolid
                End With
            Else
                With cell1.Interior
                    .ColorIndex = xlNone
                End With
            End If
        Next cell1
    
        ' Examine the cells in the second collection.
        For Each cell2 In cells2
            On Error Resume Next
            Err.Clear
            key = cell2.Row - range2.Row & "," & cell2.Column - _
                range2.Column
            Set cell1 = cells1(key)
            If Err.Number <> 0 Then
                ' The second cell is missing.
                no_match = True
            ElseIf cell2.Text <> cell1.Text Then
                ' The cells don't match.
                no_match = True
            Else
                no_match = False
            End If
    
            ' If the cells don't match, color cell2.
            If no_match Then
                With cell2.Interior
                    .Color = RGB(255, 204, 204)
                    .Pattern = xlSolid
                End With
            Else
                With cell2.Interior
                    .ColorIndex = xlNone
                End With
            End If
        Next cell2
    I also changed it to import a before and after text file instead of html files. All this is attached in a zip file if anyone is interested or can apply the code to their needs.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 10-21-2021 at 10:02 AM. Reason: fixed code tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Improve code performance to Optimize Loops
    By MusicMan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2021, 01:07 PM
  2. How to Optimize This Code?
    By therealdees in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-20-2021, 02:40 AM
  3. Trying to optimize VBA code for Excel 365
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-19-2019, 01:58 AM
  4. [SOLVED] Optimize my VBA code
    By modytrane in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-07-2019, 11:25 AM
  5. [SOLVED] optimize macro - cutting down loops and autofill
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2012, 12:56 AM
  6. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  7. if else loops excel vba code required
    By razwan1978 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-09-2009, 03:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1