Slow "comparison/replace" script

mavCZ · #1 06-11-2014, 11:35 PM

Hi everyone, this script is supposed to compare two ranges and in case of no match replace cells with text. Im quite new to VBA and when I usually write a script, it performs poorly on big data sets. Is there any way, how to make this work faster? I have tried Application.ScreenUpdating = False, but it doesnt work.

Thank you in advance!

Code:

Sub RangeCompare()
    Dim Range1 As Range, Range2 As Range, c As Range
    
    On Error Resume Next
    
    Set Range1 = ActiveWorkbook.Sheets("Master Slide").Range("a4:a90")
    Set Range2 = Application.InputBox("Select Range2:", Title:="Get Range2", Type:=8)
       
    On Error GoTo 0
    
    For Each c In Range2.Cells
    
    If Len(c) > 0 Then
        If Application.WorksheetFunction.CountIf(Range1, c.Value) = 0 Then
            c.Formula = "Others"
        End If
    End If
        
    Next c
        
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Wierd "script code" in a downloaded .doc file	CNBarnes	Word	2	10-18-2012 02:07 AM
replace data from variable with "sub and super script" from excel to word by vba	krishnaoptif	Word VBA	9	06-22-2012 05:08 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM
Rules and Alerts: "run a script"?	discountvc	Outlook	0	06-15-2010 07:36 AM
An "error has occurred in the script on this page"	decann	Outlook	8	09-03-2009 08:54 AM