For each loop very slow

**CassioGodinho** · 04-02-2012, 03:36 PM

Hello, I have the following macro

Sub Corresp()

Dim Original As Range
Dim Shift As Range

For Each Original In Range("A3:A500")
    For Each Shift In Range("F3:F500")
        If Original.Value = Shift.Value Then
            Original.Next.Value = Shift.Next.Value
        End If
    Next
Next
 

End Sub

With small column ranges its working fine, but I need it to compare columns with 3000 or more rows. The first time I ran this with 2000 rows it took a while but worked but now if I run the macro the excel crashes.
Any ideas on how to improve the macro?

**tigeravatar** · 04-02-2012, 03:55 PM

CassioGodinho,

Welcome to the forum!
What is the macro trying to do? You're updating the same cell over and over and over again until you finally go to the next cell in the A column... Seems like a lot of redundancy there, but without knowing the whole picture, it's hard to help out.

**CassioGodinho** · 04-02-2012, 04:03 PM

Hi, thanks for the reply tigeravatar.
Let me show some screenshots to explain.

Pic1.PNG

The macro is suposed to compare column A with column F, if the values match the value of column G is copied to the column B (using the .Next to copy the value to the cell in front of the match)

Pic2.PNG

The result is this. The macro works the way I want, but its very slow and "heavy", so if I compare large columns it takes forever and in most cases the excel crashes.

**tigeravatar** · 04-02-2012, 04:42 PM

CassioGodinho,

Does it have to be a macro? What you're looking for can be accomplished with a formula in cell B3 and copied down:
=IFERROR(VLOOKUP(A3,F:G,2,0),"")

If it has to be a macro, you can input the formula programmatically and then convert the results to values, no loop necessary:

Sub tgr()
    
    Application.Calculation = xlCalculationManual
    With Range("A3:A500").Offset(, 1)
        .Formula = "=IFERROR(VLOOKUP(A3,F:G,2,0),"""")"
        Calculate
        .Value = .Value
    End With
    Application.Calculation = xlCalculationAutomatic
    
End Sub

**CassioGodinho** · 04-02-2012, 04:50 PM

Oh god, thanks a lot tigeravatar.
I just started learning about vba macros that sometimes I want to make anything using a macro and forget about the power of excel formulas =)