title: Excel regex match and return multiple references in formula
body: <p>I've created a function that will return the Nth reference which includes a sheetname (if it's there), however it's not working for all instances.  The regex string I'm using is</p>
  
  <p><code>'[\w ]+[']!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5})</code></p>
  
  <p>I'm finding though it won't find the first reference in either of the below examples:</p>
  
  <pre><code>='Biscuits Raw Data'!G783/'Biscuits Raw Data'!E783
  =IF('Biscuits Raw Data'!G705="","",'Biscuits Raw Data'!G723/'Biscuits Raw Data'!G7005*100)
  </code></pre>
  
  <p>Below is my Function code:</p>
  
  <pre><code>Function GrabNthreference(Rng As range, NthRef As Integer) As String
  
  Dim patrn As String
  Dim RegX
  Dim Matchs
  Dim RegEx
  Dim FinalMatch
  Dim Subm
  Dim i As Integer
  Dim StrRef As String
  
  patrn = "'[\w ]+[']!([$]{0,1})([A-Z]{1,2})([$]{0,1})(\d{1,5})"
  
  StrRef = Rng.Formula
  
  Set RegEx = CreateObject("vbscript.regexp")       ' Create regular expression.
  RegEx.Global = True
  RegEx.Pattern = patrn                             ' Set pattern.
  RegEx.IgnoreCase = True                           ' Make case insensitive.
  Set RegX = RegEx.Execute(StrRef)
  
  If RegX.Count &lt; NthRef Then
      GrabNthreference = StrRef
      Exit Function
  End If
  i= -1
  For Each Matchs In RegX    ' Iterate Matches collection.
      Set Subm = RegX(i).submatches
      i = i + 1
      If i = NthRef -1 Then
          GrabNthreference = RegX(i)
          Exit Function
      End If
      'Debug.Print RegX(i)
  Next
  </code></pre>
  
  <p>End Function</p>