Formula returns #value error when cell contains >255 characters. How can I process text cells that might be 500 characters long?

Question

Hi,

I have a very large data set I am trying to process. One thing I am trying to do is see if a value in one worksheet exists in another worksheet. I am using

                 =MATCH(A204756,mainsheet!$A$1:$A$210764)

This formula works correctly for most of the cells. A #VALUE error occurs when the length of the cell exceeds 255.

Any suggestions on handling this would be GREATLY appreciated.

Thanks!

Answer 1

Function MyMatch(Lup As Variant, lookrng As Range) As Long

Dim c As Range

For Each c In lookrng

    If c.Value = Lup Then

        MyMatch = c.Row

    End If

Next

End Function

I would add an Exit For or even an Exit Function line after the MyMatch = c.Row. No sense processing them all if it is found in the second row. Further, a non-case-sensitive match more closely resembles the native MATCH() function.

Function MyMatch(Lup As Variant, lookrng As Range) As Long

    Dim c As Range

    For Each c In lookrng

        If LCase(c.Value) = LCase(Lup) Then

            MyMatch = c.Row

            Exit Function

        End If

    Next

End Function

Answer 2

Great idea. Thank you for your quick reply.

Answer 3

I would add an Exit For or even an Exit Function line after the MyMatch = c.Row. No sense processing them all if it is found in the second row. Further, a non-case-sensitive match more closely resembles the native MATCH() function.

Function MyMatch(Lup As Variant, lookrng As Range) As Long

    Dim c As Range

    For Each c In lookrng

        If LCase(c.Value) = LCase(Lup) Then

            MyMatch = c.Row

            Exit Function

        End If

    Next

End Function

Ah yes, good points.

Answer 4

Hi,

You can us a simple user defined function. ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in on the right. Close VB editor. Back on the worksheet call with this in a cell

=MyMatch(A204756,mainsheet!A1:A201764)

Function MyMatch(Lup As Variant, lookrng As Range) As Long

Dim c As Range

For Each c In lookrng

    If c.Value = Lup Then

        MyMatch = c.Row

    End If

Next

End Function