Compare 2 strings with Boolean search

[jconvery]

I am a newbie to VBA and am attempting to create a function which will

a) Compare 1 cell with a string to another cell with a string such that
b) If ANY of the characters in Cell1 are contained in Cell 2, a "true" result is provided, else "false."

Example 1: evaluates whether any of the letters in Cell1 are in Cell2
A1: BC
B1: CD
Result - true

Example 2: evaluates whether any of the letters in Cell1 are in Cell2
A1: AB
B1: CD
Result: false

I THINK this involves not only a boolean search but the LEN command.

Thanks so much in advance! This will help me in my learning the basics of VBA!

 

[GTO]

As a UDF, maybe:

Excel Workbook
	A	B	C
1	abcd	efbg	TRUE
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=ANYMATCHCHAR(A1,B1)

Option Explicit
    
Function AnyMatchChar(ByVal Cell1 As String, ByVal Cell2 As String) As Boolean
Dim i As Long, ii As Long
    
    If Not CBool(Len(Cell1)) Or Not CBool(Len(Cell2)) Then
        AnyMatchChar = False
        Exit Function
    End If
    
    For i = 1 To Len(Cell1)
        For ii = 1 To Len(Cell2)
            If Mid(Cell1, i, 1) = Mid(Cell2, ii, 1) Then
                AnyMatchChar = True
                Exit Function
            End If
        Next
    Next
End Function

Hope that helps,

Mark

 

[Jon von der Heyden]

Hi and welcome to the board.

Firstly I would like to point out that this can be achieved with a formula. If string1 is in A1, and string2 is in A2, then:

=SUM(--ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),0)))>0

Confirmed with Control+Shift+Enter.

But if you are looking to further your VBA, then perhaps this function will do:

Public Function AnyMatch(ByVal strFind As String, ByVal strText As String) As Boolean
    Dim lngChar As Long
    
    AnyMatch = False

    For lngChar = 1 To Len(strFind)
        If InStr(1, strText, Mid$(strFind, lngChar, 1), vbTextCompare) > 0 Then
            AnyMatch = True
            Exit For
        End If
    Next lngChar
End Function

 

[Jon von der Heyden]

@GTO: Howdy Mark. I like the title of your UDF. Great minds...

Edit: And my suggested formula could be improved too:

=SUM(--ISNUMBER(MATCH("*"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)&"*",A2,0)))>0

 

[shg]

Or

=OR(ISNUMBER(SEARCH(MID(A1, ROW(INDIRECT("1:" & LEN(A1))), 1), A2)))

 

[GTO]

@GTO: Howdy Mark. I like the title of your UDF. Great minds...

Edit: And my suggested formula could be improved too:

=SUM(--ISNUMBER(MATCH("*"&MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)&"*",A2,0)))>0

Howdy Jon,

Hope all is terrific with you and yours

I'm afraid "minds" should have been singular, as of course there's no reason to check both ways... Yikes...

Have a great day,

Mark