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

John Carney
John Carney used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
If InStr([MyCell], cel) > 0 Then
-->
If UCase([MyCell])=UCase(cel) Then
or
If [MyCell]=cel Then
John CarneyReliability Business Tools Analyst II

Author

Commented:
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
HainKurtSr. System Analyst

Commented:
so it means I could not get the issue, running the sample and will let you know if I have something...
Sr. System Analyst
Commented:
hefre it is :)
Sub FindStrings()
    [E37:K37].ClearContents
    Dim cel As Range, x As Long, y As Long, z As String
    Dim longest As String
    x = [MyCell].Row
    y = 5
    For Each cel In [Table1]
        If Len(cel) > 2 Then
            cel.Select
            If InStr([MyCell], cel) > 0 Then
                If Len(longest) < Len(cel) Then longest = cel
            End If
        End If
    Next
    Cells(x, y) = longest
End Sub

Open in new window

John CarneyReliability Business Tools Analyst II

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial