Greetings KK1966,

I'm not sure, but if I am grasping the pattern you want to match, maybe something like:

In a Standard Module:
[VBA]Option Explicit

Sub example()
Dim REX As Object
Dim rngSource As Range
Dim rngDest As Range
Dim arr_vntValues() As Variant
Dim lDestRow As Long
Dim n As Long

'// Find last cell in source column; presumes a header row in row 1. //
Set rngSource = RangeFound(Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Sheet1.Rows.Count, 1)))

'// If no data, bail... //
If rngSource Is Nothing Then
MsgBox "No values"
Exit Sub
End If

'// If data, reset range from row 2 to last row w/data. //
Set rngSource = Sheet1.Range(Sheet1.Cells(2, 1), rngSource)

'// Return first row with no data in destination. //
Set rngDest = RangeFound(Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(Sheet2.Rows.Count, 1)))
If rngDest Is Nothing Then
lDestRow = 2
Else
lDestRow = rngDest.Row + 1
End If

'// Just in case only one row of data, so no hiccup whne we go to loop the array. //
If rngSource.Rows.Count = 1 Then
ReDim arr_vntValues(1 To 1, 1 To 1)
arr_vntValues(1, 1) = rngSource.Value
Else
arr_vntValues = rngSource.Value
End If


Set REX = CreateObject("VBScript.RegExp")
With REX
'// Presuming only one return per cell //
.Global = False
.IgnoreCase = False
'// Create pattern as desired. This one is looking for one match made of two //
'// sub-expressions. The first is "STATUS', followed by zero to two spaces, //
'// followed by "CODE", followed by zero to two spaces. The second sub- //
'// expression is 1 to many digits. If we Match, then we just want the second //
'// sub-expression returned. //
.Pattern = "(STATUS {0,2}CODE {0,2})(\d+)"
For n = 1 To UBound(arr_vntValues, 1)
'// Test first, so that .Execute doesn't botch if no Match. //
If .Test(arr_vntValues(n, 1)) Then
'//If success, return the value. I tacked in including which row it was//
'// found in. //
Sheet2.Cells(lDestRow, 1).Value = _
"In Row: " & n + 1 & _
" I found: " & .Execute(arr_vntValues(n, 1))(0).SubMatches(1)
lDestRow = lDestRow + 1
End If
Next
End With
End Sub

Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function[/VBA]

By the way, where is Anguilla?

Hope that helps,

Mark