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

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...

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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