VBA Text search for specific strings

[dfolzenlogen]

Hi,
Please see the following sample data:

Name	Address_Line_One
ANNIE BELL PRYOR	3805 RADFORD
JO ANN WELLS	7513 BERMEJO RD
MARK ALAN HANCOCK &	ELIZABETH ANN HANCOCK
DOMINIQUE TRAN AND/OR	HIEN TRAN

I am trying to use VBA to look at the the Name field and if the field ends with "and/or", "* and", "* &", "* or" to concatenate the Name field with Address_Line_One field [data is from an outside source] . See below:

If UCase(Trim(.Range("M2"))) Like "*AND" Or UCase(Trim(.Range("M2"))) Like "* OR" Or UCase(Trim(.Range("M2"))) Like "*&" Or UCase(Trim(.Range("M2"))) Like "* AND/OR" Then
                wsTransfer.Range("B17") = Trim(.Range("M2")) & " " & Trim(.Range("N2"))
            Else
                wsTransfer.Range("B17") = Trim(.Range("M2"))
            End If

I can get the code to work EXCEPT when I try to include a search for "* AND/OR". I'm guessing it's because AND/OR are VBA operators. What is the correct syntax so that I can search for this text string?

Thank you.

 

[juddaaaa]

You could try using a regular expression

Dim c As Range
Dim re As Object

Set re = CreateObject("VBScript.RegExp")

re.Pattern = "\s+[AND|OR|\&]"

With ActiveSheet
    If re.test(.Range("M4")) Then
        wsTransfer.Range("B17") = .Range("M4") & " " & .Range("N4")
    Else
        wsTransfer.Range("B17") = .Range("M4")
    End If
End With

 

[DanteAmor]

Try this

  Select Case True
    Case UCase(Right(.Range("M2"), 4)) = " AND", UCase(Right(.Range("M2"), 3)) = " OR", _
         UCase(Right(.Range("M2"), 6)) = "AND/OR", Right(.Range("M2"), 1) = "&"
      wsTransfer.Range("B17") = Trim(.Range("M2")) & " " & Trim(.Range("N2"))
    Case Else
      wsTransfer.Range("B17") = Trim(.Range("M2"))
  End Select

 

[dfolzenlogen]

Thank you for your replies. I had finally gotten the formula to work using the formula below but I'm going to give your methods a try as well.

            If UCase(Trim(.Range("M2"))) Like "* AND" Or UCase(Trim(.Range("M2"))) Like "* OR" Or UCase(Trim(.Range("M2"))) Like "* &" _
            Or UCase(Trim(.Range("M2"))) Like "* FBO" Or UCase(Trim(.Range("M2"))) Like "* F?B?O" Or UCase(Trim(.Range("M2"))) Like "* DBA" _
            Or UCase(Trim(.Range("M2"))) Like "* D?B?A" Or UCase(Trim(.Range("M2"))) Like "* AND?OR" Then
                wsTransfer.Range("B17") = Trim(.Range("M2")) & " " & Trim(.Range("N2"))
            Else
                wsTransfer.Range("B17") = Trim(.Range("M2"))
            End If

 

[DanteAmor]

Here is another approach for you to consider:

  Dim a As Variant, i As Long
  a = Array("* AND", "* OR", "* &", "* FBO", "* F?B?O", "* DBA", "* D?B?A", "* AND?OR")
  wsTransfer.Range("B17") = Trim(.Range("M2"))
  For i = 0 To UBound(a)
    If UCase(Trim(.Range("M2"))) Like a(i) Then wsTransfer.Range("B17") = Trim(.Range("M2")) & " " & Trim(.Range("N2"))
  Next

 

[dfolzenlogen]

Above and beyond!! Thank you!!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback!