Good afternoon

I am trying to extract key information from a large data set. The data is in string format and a regular expression will pull the required data quickly.

The sub routine works excellent when I simply print it to the immediate window.
However, I would like to return the match to an array and then eventually to a range on a worksheet.


Option Explicit
Option Base 1


Sub useRegularExpressions()

'create new regular expression object

Dim regEx As New RegExp
regEx.Global = True
regEx.Pattern = "\d{4}\S?" 'this will find the four numbers and letter for each tag

'initialize the source data array from a specificed range of cells.
Dim sourceArray() As Variant
sourceArray = ActiveWorkbook.ActiveSheet.Range("A13:A1072").Value 'Range("A13:A1072")

'there is exactly one expected match per cell in the source data, the destination range must be the exact size of the source array
Dim destArray() As Variant
ReDim destArray(UBound(sourceArray, 1))

'iterate through the source data
Dim i As Integer
i = 1

For i = 1 To UBound(sourceArray(), 1)

    Dim mc As matchcollection
    Set mc = regEx.Execute(sourceArray(i, 1))
    
    Dim item As Variant
    

    For Each item In mc
        Debug.Print sourceArray(i, 1), item.Value 'this step works!
        destArray(i, 1) = item.Value 'this step doesnt work!
                              
    Next item
    
Next i

ActiveWorkbook.ActiveSheet.Range("M13:M1072").Value = destArray

End Sub