VBA RegEx identifiying multiple patterns - Excel
<p>I´m rather new to VBA RegEx, but thanks to this <a href="https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops">stackoverflow thread</a>, </p>

<p>I am getting to it. I have a problem and hope that somebody can help. In row 1 in Excel I have multiple Strings with different city/country attribution. Example:</p>

<pre><code>A1: "/flights/munich/newyork"
A2: "flights/munich/usa"
A3:  "flights/usa/germany"
...
</code></pre>

<p>What I wanna have now, is a VBA that goes though those strings with RegEx and prompts a categorisation value if the RegEx is met. Example:</p>

<pre><code>A1: "/flights/munich/new-york" categorises as "city to city"
A2: "flights/munich/usa" categorises as "city to country"
A3:  "flights/usa/germany" categorises as "country to country"
</code></pre>

<p>Right now, I have a code that will return the "city to city" category to me, but I can´t figure out who to get a code that handles the multiple patterns and returns the corresponding output string.</p>

<p>In short, a logic like this is needed:</p>

<p>If A1 contains RegEx <code>".*/munich/new-york"</code> then return output string <code>"city to city"</code>, if A1 contains RegEx <code>".*/munich/usa"</code> then return output string <code>"city to country"</code> and so on.</p>

<p>Guess this has something to to with how to handle multiple if statements with multiple patterns in VBA, but I can´t figure it out. </p>

<p>This is how my code looks right now - hope you can help!</p>

<pre><code>Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "(munich|berlin|new-york|porto|copenhagen|moscow)/(munich|berlin|new-york|porto|copenhagen|moscow)"

    If strPattern &lt;&gt; "" Then
        strInput = Myrange.Value
        strReplace = "CITY TO CITY"

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "NO MATCH FOUND"
        End If
    End If

End Function
</code></pre>