Pass from a for cicle to a search cicle

**fabdav** · 07-21-2014, 04:10 AM

To increase performance I would like to optimize the following macro. Probably passing from a for cicle to a search.
Any suggestion?

Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range("A1:c322000").Value
DataRange1 = Range("J1:J322000").Value
For Y = 1 To 234100
For Irow = 1 To 322000

MyVar = DataRange(Irow, 1)
MyVar2 = DataRange1(Y, 1)
  If MyVar = MyVar2 Then
DataRange(Irow, 3) = "X"
Debug.Print Y
GoTo yy
Else
End If


Next Irow
yy:
Next Y


Range("A1:C1000000").Value = DataRange

End Sub

**Olly** · 07-21-2014, 04:46 AM

Try:

Sub foo()
Dim rng1 As Range, rng2 As Range, c As Range

Set rng1 = Sheet1.Range("A1:A322000")
Set rng2 = Sheet1.Range("J1:J234100")

Application.ScreenUpdating = False

For Each c In rng1
    If Not c.Value = "" Then
        If Not rng2.Find(c.Value, lookat:=xlWhole) Is Nothing Then c.Offset(0, 2).Value = "X"
    End If
Next c

Application.ScreenUpdating = True

End Sub

**Hawkeye16** · 07-21-2014, 04:50 AM

I am not sure the for loop is your biggest problem. Is there a reason you are pulling in 322,000 rows for each array every time. Is there a set number of entries? If there is usually less than that you can just pull in what is needed and run through that (since you only run through 234,100 for the bigger array anyway).

You can also move certain things around to require less calculations such as moving the MyVar = DataRange(Irow,1) before the second for loop starts. This will take out (234,100 x 321,999) calculations that the macro has to run.

I like Olly's solution. Nice and simple. No pulling data into arrays.