Application match doesn't work with long strings

[YasserKhalil]

Hello everyone
I have some base64 strings in column A and I have tested the Application Match but it seems it doesn't work with long strings (I am not sure)

Code: Select all

Sub Test()
    Dim x, s As String
    s = Range("A49").Text
    x = Application.Match(s, Columns(1), 0)
    If Not IsError(x) Then
        Debug.Print x
    End If
    
    
    Dim r As Long
    For r = 1 To 100
        If Cells(r, 1).Value = s Then Debug.Print r
    Next r
End Sub

The variable x returns Error 2015 while when using loops I could return the row number of the string correctly
Any ideas how to use match with long strings?
Example:

Code: Select all

iVBORw0KGgoAAAANSUhEUgAAAJYAAAAyCAIAAAAx7rVNAAAJfklEQVR42u2cZ1QVRxiG13bsIscCKMejgiIqCKJeLEdALEgUjaJoFAwWNCpqBBRjISiW2EHRKJYoikaxtyh20dhjN/Zu7L3rj7x35mbY7Ny73AX5cc+d9wdnz5a5e/bZ753vm5lF8iVqRtS8efMWLVq0bNmyVatWAQEBrVu3/oaobdu2QUFB7dq1+5aoY8eOwcHBnTp16ty5c0hISJcuXb4j6t69e2hoaFhY2PdEPXv27NWrV+/evfv06RMREdG3b98fiAYMGDBw4MDIyMhBgwYNHjz4R6KoqKjo6OiYmJhhw4YNHz48Njb2J6JRo0aNHj16zJgxPxONHTt23LhxCQkJ48ePnzBhwsSJE38hmjJlytSpU6dNmzZ9+vQZM2YkJiYmJSXNmjVr9uzZycnJc+bM+ZVo/vz5KSkpCxYsWLhw4aJFi34jWrp0aWpq6rJly5YvX56WlrZixYrfiVavXp2enr5mzZq1a9euW7duw4YNGzdu3LRp0+bNm7ds2bJ169Y/iHbs2JGRkbFz585du3bt3r177969+/bt279//4EDBzIzMw8ePPgn0ZEjR44ePXrs2LHjx4+fOHHiL6LTp0+fOXPm7Nmz586dO3/+/IULF/4munz58pUrV65evXrt2rXr16/fvHnz1q1bt2/fvnPnzt27d+/du/cP0cOHDx89eiSBn5+fH/j5+/szhAFEgYGB4NemTRuKsH379uDXoUMHhjCEqGvXruDXrVs3OcLw8HCGMIKoX79+4Ne/f3+GEPyGDBkCfkOHDmUIhxONGDEC/EaOHClHGB8fzxBOIJo0aRL4TZ48mSEEv5kzZzKEyURz584Fv3nz5jGE4Ld48WLwW7JkCUOYRrRy5UrwW7VqFUMIfuvXr2cItxBt27YN/LZv384Qgt+ePXsYwkyiQ4cOgd/hw4cZwhNE4Hfq1CmG8DzRxYsXwe/SpUsMIfjduHGDIbxLdP/+ffB78OCBEYQtiFgIMoRBRAxhMBELQYYwlKhHjx4UYS8iFoIM4UAiGoIMYTQRC0GGcDRRXFwcRTiOiIUgQziViIYgECYRsRBkCFOIaAgyhKlELAQZwnQiGoJAuJGIhSBDmEFEQxAI9xGxEGQIjxLREDx58iRFeIaIhSBDeIWIhiAQ3iJiIcgQPiKShItatIs+fvxYEi5q0S76P4R57aI4iva9vLxcXFyqVq3q7Ozs5ubWtGlTNCVcNMcu+uTJEymvXRTUPTw8bG1tJdPKly+fk5MTXhpEpHBRTS5qQJgXLoqrpJxKuKj5Lvr06VMpL1wUTUm5kHBR813UgPCru2j9+vVziVC4qJkuqkeYFy6KhCU3CIWLmu+iBoRfPRetUKFCLhGa6aIUoTW76LNnz6RsXRQIsY0QbPmfdDodagPGic9FS5UqVa5cuQYNGoC0qYoel1SsWLFAgQIKfkhfFS6KDbw09erVq1atWtGiRQsWLEivwt9ChQqhaLFmF33+/Lmk4qJQo0aNQMvV1bVs2bL29vZgg6eWP39++UPnK3rsUa/osYG6ELWEgh/AozRkCFHygyiPWS6ULtbsonqEKi6KPXZ2dvQJ8o9bjlDTuCg2UNEjmBTN4odwFep6AMZGnTp16Dnqwm9Zs4u+ePFCUnFR7DSnV9M0Loq/8OQSJUpQfnKEuBOU9kiXsoWHy/38/JDj0KLCml00C6HRXBR/0QPZ2NioW5mmcVHEa+XKlemF8GTWSKVKlXAO7gFeqoh49H+Ojo41a9aEqyOrQj9KKwqRi2YhVMlFsRMPrnbt2vRpokd0cnJSIDTfRbHH29ubRpicH7ZxOe4Bfa2CH34OqS/YI68RFT3vonqE5lT0UJUqVQAbmSGyGwVC810ULZcuXZoPQdoyi055aIKcqOhVXPTly5eS1ore3d2dR2iOi+Jo9erVaZDBG9nlDg4OCLLGjRsXKVJE0TIaERW9uovqEWqt6NEnGUWYrYsiC6WQUJMwt8QGLUJQ8ymaBVoxLpqti7569UrSOi5ao0YNHmG2LooLWXIrt1DYcmxsLH60fPnyimZ9fX3RDk4oU6YM8in0oDgHXSmyVuGiLAT1CLWOi/Lhkq2LYgMk6ICAfFigcOHCiE4UggjQ4sWLK5rFUVM5sE6nQzgKFwXC169fS1rHRZ2dnXmE6i6KFpDKMudkF/r4+NA5epQu5lTxciG9Qo8oXDQLofmzSzxCdRcFSJTq/BAPwi4qKorO0detW1dl9Ede0ctJIwMSLmpAqGl2CVmlKYRGXRSNoDPjeTRr1oytdEIUGkWIohBVKV6akiVLUgeGu8qtGJdbuYvqEWqdXeLTGRUXBUVPT09+cAdFBQ1BirBhw4aKoXPI1dU1Li4O6WhMTAwKRzZqI28NKY+Vu+ibN28krXP0tWrV4hGaclG0gEfPhxc8UL5eFL/OpzPR0dGsnIiPj0f9amNjwxceVu6ieoRa5+jRsRlFyLsoNoyGF/aArny9KN4SvqgANnlFj9yVjfPJR+as3EXfvn0raZ2j59fFmHJRnOzo6MiHoIuLi2K9KHjDNvlyU17Ro8ZHCas4B6Zq5S6qR6h1pRM8kJ905V0UwhM3WiqgZcWqewgnK+K1SZMminFRvEaKpmxtba3cRd+9eydpXemETJJHyLsooHp4ePD8YH101bZi1T3Op4krS02RfOIoG1TD37CwMEVr3t7eVu6iWQiNuij2eHl56XQ6nMBWOuF8ft6cd1GcabSWgGEaXXWPbX9/f0VpgXIiISGBIkRqgxtTtIa01spd1IDQlIuCqJ2dHavEUU7A7nDIFEJ5LgrefCJD55VMfbsEHnzOaW9vj7oTCHGmomfF6yVcVI9Q3UVztpwQsE0t6IYnq3y7hPcA/mnOSA1wIjqFi75//15Sz0V9fHzgZloHMIEQISKfkZBXe+rfLiF1QpKiWFajkLu7O7IbkYsaEJqTi2qNRSB0c3MzCj7bL0ABEjfAJvcVKlasGG4yMTFRVPTURT98+CBpXXWPnUhwHBwcjD5i7EcLkZGRqD1QFYSHh+fsC1CcjxaQEOE9QJ+KgEY3iVQIN4B+UYyLshDUI8zxqntsBwQEoNJHzFUhQs3u6+uLo1/lO3ocQlNIUz09PWHLgYGBaJPyE+OiDOHHjx8l8R29RbuoAaH4jt5yXVSJUPw3EotzUT1C4aIW7aKfPn2ShItatIv+D6FwUUt00c+fP0vCRS3aRQ0IhYtarot++fJFEi5q0S5qQChc1HJdVI9QuKhFuyj0L+odweNvTcwrAAAAAElFTkSuQmCC

[rory]

Use your loop? Match will not work with strings over 255 characters.

[YasserKhalil]

Thanks a lot for the information. Is there any workaround to make it faster as using loop takes too much time.

[rory]

Are you looping cell by cell, or using arrays? How much time is "too much"?

[HansV]

If you store the cell values in an array, the loop should be faster:

Code: Select all

Sub Test()
    Dim s As String
    Dim v As Variant
    Dim r As Long
    s = Range("A49").Text
    v = Range("A1:A100").Value

    For r = LBound(v) To UBound(v)
        If v(r, 1) = s Then Debug.Print r
    Next r
End Sub

[YasserKhalil]

Thanks a lot, Mr. Hans
This is what I did exactly by using arrays
But I wonder if is there an equivalent method to Application. Match?

[HansV]

Unfortunately, the Find method of the Range object also fails with strings longer than 255 characters...

[rory]

You could use Evaluate with a formula using LOOKUP but I'm honestly surprised that looping through an array takes that long. How long is it taking, and how are you populating the array?

[SpeakEasy]

Depending on the version of Excel that you have, you may be able to use XMATCH, which does not suffer the same limit

[DocAElstein]

Hello Yasser,
_(i) If you are only expecting to find one occurrence then remember to get out of any loop when you find it - that will make it a lot quicker if its not far down , or along, or whatever. (Whever this is any use will depend on exactly what you are doing)

_(ii) You might want to check if a Do While is quicker. It might be sometimes, although I doubt there will be much difference in it.

Code: Select all

Sub TestA()  '  https://eileenslounge.com/viewtopic.php?p=303483#p303483
Dim Es As String, Vee() As Variant, Ahh As Long
 Let Es = Range("A49").Text
 Let Vee = Range("A1:A100").Value
    For Ahh = LBound(Vee()) To UBound(Vee())
        If Vee(Ahh, 1) = Es Then Debug.Print Ahh: Exit For
    Next Ahh
End Sub
Sub TestE()  '  https://eileenslounge.com/viewtopic.php?p=303483#p303483
Dim Es As String, Vee() As Variant, Ahh As Long, FandIt As String
 Let Es = Range("A49").Text
 Let Vee = Range("A1:A100").Value
Dim Cnt As Long
    Do While FandIt = ""
     Let Cnt = Cnt + 1
        If Vee(Cnt, 1) = Es Then Let FandIt = "Fand it at " & Cnt
     Loop ' While FandIt = ""
 Debug.Print FandIt
End Sub

_(iii) Another thing which might not be practical depending on what you are doing… .. Can you look for part of the string?

Code: Select all

Sub FindIt()  '   https://eileenslounge.com/viewtopic.php?p=303497#p303497
Dim Rw As Long
 Let Rw = Columns(1).Find(What:=Left(Range("A49"), 255)).Row ' Default for   .Find   is  LookAt:=xlPart.
' Let Rw = Columns(1).Find(What:=Left(Range("A49"), 255), LookAt:=xlWhole).Row ' This will error
End Sub

Alan

[YasserKhalil]

Thanks a lot, Mr. Alan
The second code is more suitable for my case

Another question: I tried the function CountIf but it reurns VALUE error, it seems this doesn't work too with long strings. What is the alternative in that case for CountIf?

[SpeakEasy]

If you don't have XMATCH (as mentioned above), then there is another workaround for your original function that avoids loops and works pretty much as originally planned

Simply change

x = Application.Match(s, Columns(1), 0)

to

x = Application.Evaluate("=Match(True, Index(A:A=A49, 0), 0)")

[YasserKhalil]

Amazing. That's exactly the simplest and perfect solution.
Thank you very much.

[YasserKhalil]

When using the value as a variable, it returns an error

Code: Select all

x = Application.Evaluate("=Match(True, Index(A:A=" & s & ", 0), 0)")

[SpeakEasy]

Code: Select all

x = Application.Evaluate("=Match(True, Index(A:A=""" & s & """, 0), 0)")

But just be aware that this is designed to work when s is a string, as per your original post (If s is numeric then your version above would work. But then, if s is a numeric, the code in your original post should work ...)

[YasserKhalil]

This doesn't work too. Can you try it on your side?

[adeel1]

Hi, sorry for interruption

what is advantage of using index "Index(A:A=""" & s & """, 0)" although return of A:A=""" & s & """ is also in True/False and Index(A:A=""" & s & """, 0) this will also return True/False

Adeel

[SpeakEasy]

>This doesn't work too

Really? What error are you getting?
And can you confirm that it works fine for when NOT using a variable, which is what you implied earlier

Personally, rather than setting s to the text of a cell I'd be tempted to set it to the Range of the cell itself, and then use

Code: Select all

x = Application.Evaluate("=Match(True, Index(A:A=" & s.Address & ", 0), 0)")

>what is advantage of using index
There isn't one - good call. You can remove the Index function, so:

Code: Select all

x = Application.Evaluate("=Match(True,A:A=" & s.Address & ", 0)")

for the non-variable version

[adeel1]

Code: Select all

Set s = Range("c2")
xx = Evaluate("=MIN(IF(A:A=" & s.Address & ",ROW(A:A)))")

[YasserKhalil]

But actually, I have the string in a variable, not in a range. So this doesn't work for me.