Thread: Using a custom function using instr

Hi. I'm trying to implement a customized function in Excel, but I've hit a dead end and can't get much further without some help. Basically, i am searching through a column of data and comparing the data to another worksheet range of data. Within the strings of the first set of data are substrings that should match on the other worksheet. Is a function possible here?

Function Crosscheckerss(datum)
Dim nombre As Range
    Set nombre = Sheets("NAME2").Range("A2:A31195")
For i = 0 To 31194
If InStr(1, LCase(datum), LCase(nombre(i))) > 0 Then
    Cell.Value = nombre(i, 2)
    nombre(i, 2) = Crosscheckerss
End If
Next i

End Function

Not to sure what you are trying to do.

However try this

Option Explicit

Function Crosscheckerss(datum As String)
    Dim Cell As Range
    Dim LastRow As Long
    
    LastRow = Sheets("NAME2").Range("A" & Rows.Count).End(xlUp).Row
        
    For Each Cell In Sheets("NAME2").Range("A2:A" & LastRow)
        If InStr(1, LCase(Cell), LCase(datum)) > 0 Then
            Crosscheckerss = Cell
            Exit For
        End If
    Next
    If Crosscheckerss = "" Then
        Crosscheckerss = datum & " not found"
    End If

End Function

Is that what you are looking for?

This seems to have some functionality which is helpful. I wonder if it would help if I posted the macro i am using. The Macro is too slow and I thought perhaps a function would run faster. As you can see, I will go through a lot of data to find instr matches. Here is the code and a sample workbook so you can see where I'm headed. Thanks.

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

Please try the Macro from sheet called 'data' to see what it does. Thanks

Hmm?

You have marked this thread as [SOLVED] but seem to be asking a question that infers that it isn't.

I can't make sense of your sub.
Can you post a sample workbook that demonstrates how it works?

If it works for you and it is slow, then I suggest that's because you are searching through 31194 rows of data when it might not be necessary.

Try defining the range as in the function example I gave you.

i.e.

    LastRow = Sheets("NAME2").Range("A" & Rows.Count).End(xlUp).Row
    Set nombre = Sheets("NAME2").Range("A2:A" & LastRow)

Then

    For i = 0 To LastRow
        ' your code
    Next

Originally Posted by Marcol

Hmm?

You have marked this thread as [SOLVED] but seem to be asking a question that infers that it isn't.

I can't make sense of your sub.
Can you post a sample workbook that demonstrates how it works?

If it works for you and it is slow, then I suggest that's because you are searching through 31194 rows of data when it might not be necessary.

Try defining the range as in the function example I gave you.

i.e.

    LastRow = Sheets("NAME2").Range("A" & Rows.Count).End(xlUp).Row
    Set nombre = Sheets("NAME2").Range("A2:A" & LastRow)

Then

    For i = 0 To LastRow
        ' your code
    Next

Sorry for the late reply--I am uploading a spreadsheet. If you go to the data page and run the macro you can see more of the macro functionality. I think you are right about the waste of time going through all 33,000+ cells when an earlier match should just end the run. I have tried implementing the LastRow feature into the i = 0 To LastRow with no luck. THanks for your help.