Results 1 to 19 of 19

Make code faster/better best practices

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,712

    Make code faster/better best practices

    Hi all: I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.

    Sub compare_lists(droparray() As String, addarray() As String, userrange1 As Range, userrange2 As Range)
    
    Dim Rng As Range
    Dim x As Long
    Dim cellitem As Range
    Dim junk1 As String
    Dim y As Integer
    Dim junk2 As Boolean
    
    x = 1
    'Compares Current list of students to "new" downloaded list of students
    For Each cellitem In userrange1
                'This is the downloaded new list of students
             With userrange2
                 Set Rng = .Find(What:=cellitem.Value, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    'junk1 = Rng.Offset(0, 0).Value
                Else
                    droparray(x) = cellitem
                    x = x + 1
                End If
            End With
    Next cellitem
    
    
    x = 1
    'Compares New List of students to current list of students
    For Each cellitem In userrange2
            'This is the downloaded new list of students
             With userrange1
                 Set Rng = .Find(What:=cellitem.Value, _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                    'junk1 = Rng.Offset(0, 0).Value
                Else
                    addarray(x) = cellitem
                    x = x + 1
                End If
            End With
    Next cellitem
    
    End Sub
    Last edited by welchs101; 12-29-2008 at 10:17 PM.

Thread Information

Users Browsing this Thread

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

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