Speed up a macro which uses instr function

**drcheaud** · 08-16-2011, 06:46 PM

I am trying to speed up this macro. You can see lots of data is sifted through. Basically, the macro will go through a column of data (ce) (quite large) and for each row will find a string match based on another column in another worksheet (NAME2). Once a match is found the value found is inserted a few few rows over from the (ce) at hand This macro works great as is. However, I'm beginning to deal with huge amounts of data and need a much faster process. I have played around with Exit For but get errors related to "no For in Next". I believe much time is wasted sifting through all i values (0 To 30000+) when an early exit might be ideal once a match is found. Thanks. Here is the code below:

Sub crosscheck()
Dim nombre As Range
    Set nombre = Sheets("NAME2").Range("A2:A31335")
For Each ce In Range("A1:A101218")
'Nombre
    For i = 0 To 31334
        If InStr(1, LCase(ce), LCase(nombre(i))) > 0 Then ce.Offset(0, 3).Value = nombre(i, 2)
        Next i
Next ce
End Sub

**tlafferty** · 08-16-2011, 06:57 PM

You could use a native Excel formula for this which will generally outperform VBA for speed. Can you post a sample book for me to play with?

**drcheaud** · 08-16-2011, 07:17 PM

Originally Posted by tlafferty

You could use a native Excel formula for this which will generally outperform VBA for speed. Can you post a sample book for me to play with?

I have posted a sample work book. I hope it helps. Thanks

**drcheaud** · 08-16-2011, 07:33 PM

Originally Posted by drcheaud

I have posted a sample work book. I hope it helps. Thanks

Sorry PLEASE see workbook below for more ideal speed conditions. (This has much more data to sift through in NAME2. you'll see what i mean by just slightly faster than 1 row per second when the macro is run from the data page. Thanks again.

**drcheaud** · 08-17-2011, 12:07 PM

Hi Thomas:

Any luck playing around with this one?

Thanks,

DrCheaud

**Richard Buttrey** · 08-16-2011, 07:03 PM

Hi,

Since you are processing a large amount of cells you may be better advised to write an Excel function in the offset column which will deduce this match. Once you've worked out the function, store it in a named cell at the top of the column out of the way then just get your macro to copy that cell and paste it to all the 31334 cells in one hit. Finally copying and range valuing the column when that's done.

It's always better to use standard Excel functionality where you can.
If this isn't practical for you the other option is to read the A column range into a VBA array and process it in VB first before finally writing the updated array back to the worksheet.

The problem with a loop is that every iteration jumps back to the Excel App and then back to the VBE. It's this jumping backward and forward that carries a large time overhead.

Regards,

**drcheaud** · 08-16-2011, 07:15 PM

Here is a sample work book crunched down to just a few data on each sheet. The current speed I can generate the writing is slightly faster than one row per second. I have included a working macro as well.
Thanks