title: VBA RegEx identifiying multiple patterns - Excel
body: <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>