Thread: A more efficient code for custom function

I am trying to use a custom function to search instr. This works well for the first 5000 cells or so, then gets REALLY slow (calculating, processor(1), etc. Any suggestions to speed things up? I call a macro which then places a formula in each required cell. Here is the macro called (D Range is actually about 1 million rows or so, but I've been doing just 1000 at a time):

Sub callertocrosscecker()
Range("D11000:D12000").Formula = "=Crosscheckerincell(R[0]C[-3])"
End Sub

And here is the custom formula (NAME2 and DRange are in alphabetical order at the moment--wonder if this is what causes a faster time at the beginning of DRange (D1:D5000) when compared to the later cells:

Function Crosscheckerincell(datum As String)
    Dim Cell As Range
    'Dim LastRow As Long
    
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
   
    
    LastRow = Sheets("NAME2").Range("A" & Rows.Count).End(xlUp).Row
        
    For Each Cell In Sheets("NAME2").Range("A2:A" & LastRow)
        If InStr(1, LCase(datum), LCase(Cell)) > 0 Then
            Crosscheckerincell = Cell(, 2)
            Exit For
        End If
    Next
    If Crosscheckerincell = "" Then
        Crosscheckerincell = " "
    End If
  Application.Calculation = xlAutomatic
  Application.ScreenUpdating = True
End Function

Rather than looping through every cell to check for the string it would be far quicker to use a .Find method.

So...

Function Crosscheckerincell(datum As String)
    Dim Cell As Range
    'Dim LastRow As Long
    
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
   
    Set Cell= Sheets("NAME2").Columns(1).Find(datum, Lookin:=xlValues, Lookat:=xlPart)

     If Not Cell Is Nothing Then
       Crosscheckerincell = Cell(, 2) 'I don't know what this does, by the way - is it the same as Cell.Offset(0,2)?
    Else
        Crosscheckerincell = " "
    End If

  Application.Calculation = xlAutomatic
  Application.ScreenUpdating = True
End Function

Should be a bit quicker.

datum will include entire string, while the cell matched on NAME2 sheet will contain a substring of the entire string e.g. substring 'phrase' in NAME2 will be found somewhere in entire string 'use a phrase' from the datum. Hope this is as clear as mud

Originally Posted by drcheaud

datum will include entire string, while the cell matched on NAME2 sheet will contain a substring of the entire string e.g. substring 'phrase' in NAME2 will be found somewhere in entire string 'use a phrase' from the datum.

In that case my code won't work - it will find a cell which contains the entire string within Datum, plus any other text.

Maybe the way to attack this is tell us what you're trying to do, to see if there's a better way than custom functions.

I've been timing some of the runs for this macro and function. It seems very optimal while doing 100-250 rows at a time. Is there a way to force a break in the macro? For example, go through Range(D1:D100000) but break each 250 steps? shg, I will look into autofiltering for contains. Andrew, I suppose your code may work if we search turned around the process (search the datum for the substring found in the cell data)?

If you're going to use any sort of function (custom or in-built) it would make sense to find the values of the smaller data set in the larger set, rather than the other way around - 35,000 function calls have to be quicker than 1 million calls.

Sorry, another thought - just how dynamic is your data? If it only changes infrequently then you'd be better off with a macro to run through the big list and put the values in the cell where you currently have formula. Sure, it might take a while to run, but once is has you won't take a hit every time the sheet recalculates.

I have multi thread processing enabled for Excel. I'll have to look into processors perhaps if they would significantly improve run time. My data for NAME2 does not change but the other (larger set) will change completely.

But how often will it change?