Avatar of John Carney
John Carney
Flag for United States of America asked on

Macro that returns only the full string when searching a table containing partial strings as well

Please take a look at the attached workbook and let me know how to modify the macro below so that it returns only the full string when its presence is detected in a table that includes partial strings as well.

In fact if there is a simpler way to zero in on any combo of 2 or more contiguous letters drawn from a set string (in this case "ABCDEFGHJKL") without the need for the table in the first place, please let me know that as well.


Thanks,
John
Sub FindStrings()
Dim cel As Range, x As Long, y As Long, z As String
x = [MyCell].Row
y = 50
    For Each cel In [Table1]
    If Len(cel) > 2 Then
    cel.Select
         If InStr([MyCell], cel) > 0 Then
        Cells(x, y).End(xlToLeft).Offset(0, 1) = cel
        End If
    End If
    Next
End Sub

Open in new window

Build-Tables.xls
Microsoft Excel

Avatar of undefined
Last Comment
John Carney

8/22/2022 - Mon
HainKurt

If InStr([MyCell], cel) > 0 Then
-->
If UCase([MyCell])=UCase(cel) Then
or
If [MyCell]=cel Then
John Carney

ASKER
Hi Kurt, thanks. That doesn't seem to address the problem. Basically in the instance in the workbook the macro finds 3 values: DEF      DEFG and EFG. I'm not concerned about Case. I just want the macro to return only the longest string: DEFG. Maybe that's a better way to put it: Give me only the longest string that the macro can find.

Did you try it in the demo workbook?

Thanks,
John
HainKurt

so it means I could not get the issue, running the sample and will let you know if I have something...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
HainKurt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
John Carney

ASKER
Thank you so much, Kurt, this is great! Here's where I've gone with it so far. I'll have a few more followup questions tomorrow.

- John


Sub FindStrings()
[E37:E41].ClearContents
Dim vCel As Range
For Each vCel In [D37:D41]
vCel.Select
    Dim cel As Range, x As Long, y As Long, z As String
    Dim longest As String
    longest = ""
    x = vCel.Row
    y = 5
    For Each cel In [Table1]
        If Len(cel) > 2 Then
            cel.Select
            If InStr(vCel, cel) > 0 Then
                If Len(longest) < Len(cel) Then longest = cel
            End If
        End If
    Next
    Cells(x, y) = longest
 Next vCel
 [J37:J41].Calculate
End Sub

Open in new window