VBA Transferring array to a sheet

Question

I'm getting a

Run-time error '1004': Application-defined or object-defined error

Here are the relevant lines of code:

ReDim checkedArr(1 To nKeeps, 1 To nCols) As Variant
' A couple loops between here
Worksheets(sheet + "_tmp").Range("A1").Resize(UBound(checkedArr, 1), UBound(checkedArr, 2)).value = checkedArr

I think I'm doing something subtly incorrect but I can't figure out exactly what the issue might be. VB keeps automatically changing my ".Value" to ".value" in the above code snippet and I'm not sure why but it appears that maybe it's not recognizing it as a proper Range object.

I've tried explicitly declaring a range:

Dim dest As Range
Set dest = Worksheets(sheet + "_tmp").Range("A1").Resize(UBound(checkedArr, 1), UBound(checkedArr, 2))
dest.value = checkedArr

but this returns the same issue.

In the Watch, checkedArr is Type Variant/Variant(1 to 17, 1 to 41) and dest is of type Range/Range. When I expand dest (clicking the + in the watch), it doesn't even have a .Value property! There's a Value2 property that is of type Variant/Variant(1 to 17, 1 to 41), but trying to use that doesn't work either (gives the same error).

Can someone help me understand my flaw?

EDIT:

Here's the entire sub if anyone thinks the issue could be in the rest of the body.

Sub findMatches(sheet As String)

Worksheets(sheet).Activate
Dim dataArr() As Variant
dataArr = Worksheets(sheet).Range("A1").CurrentRegion.value

Dim nRows As Long, nCols As Long, nKeeps As Long, mcvCol As Long
Dim row As Integer, col As Integer, eqCrit As Boolean
nRows = UBound(dataArr, 1)
nCols = UBound(dataArr, 2)
mcvCol = getColNum("MC Value", sheet)

' matchStatus(i) will be:
' -2 for matched rules
' -1 for the header
' 1 for an orphan
' 2 for an MC Value mismatch
ReDim matchStatus(1 To nRows) As Integer
matchStatus(1) = -1
nKeeps = 1
matchStatus(nRows) = 1

For row = 2 To nRows - 1
    If matchStatus(row) = 0 Then
        eqCrit = True
        For col = 9 To nCols
            eqCrit = eqCrit And (dataArr(row, col) = dataArr(row + 1, col))
        Next col
        If eqCrit Then
            If dataArr(row, mcvCol) = dataArr(row + 1, mcvCol) Then
                matchStatus(row) = -2
                matchStatus(row + 1) = -2
            Else
                matchStatus(row) = 2
                matchStatus(row + 1) = 2
                nKeeps = nKeeps + 2
            End If
        Else
            matchStatus(row) = 1
            nKeeps = nKeeps + 1
        End If
    End If
Next row
If matchStatus(nRows) = 1 Then
    nKeeps = nKeeps + 1
End If

ReDim checkedArr(1 To nKeeps, 1 To nCols) As Variant
Dim keepIdx As Long
keepIdx = 1
For row = 1 To nRows
    If matchStatus(row) > -2 Then
        checkedArr(keepIdx, 1) = matchStatus(row)
        For col = 2 To nCols
            checkedArr(keepIdx, col) = dataArr(row, col)
        Next col
        keepIdx = keepIdx + 1
    End If
Next row

Application.DisplayAlerts = False
Worksheets(sheet).Delete
Application.DisplayAlerts = True

Sheets.Add.Name = sheet + "_tmp"
Dim dest As Range
'Set dest = Worksheets(sheet + "_tmp").Range("A1:" + Split(Cells(, nCols).Address, "$")(1) + CStr(nKeeps))

Set dest = Worksheets(sheet + "_tmp").Range("A1").Resize(UBound(checkedArr, 1), UBound(checkedArr, 2))
dest.value = checkedArr

'Set dest = Worksheets(sheet + "_tmp").Range("A1")
'dest.Resize(UBound(checkedArr, 1), UBound(checkedArr, 2)) = checkedArr
'Worksheets(sheet + "_tmp").Range("A1:" + Split(Cells(, nCols).Address, "$")(1) + CStr(nKeeps)) = checkedArr

End Sub

Answer 1

I "rephrased" your code to a 'test' sub. Have a look. Hope it helps.

    Sub test()

        Dim nKeeps As Integer, nCols As Integer

        nKeeps = 3
        nCols = 4


        ReDim ar(1 To nKeeps, 1 To nCols) As Variant

        For nKeeps = 1 To 3
            For nCols = 1 To 4
                ar(nKeeps, nCols) = nKeeps * nCols
            Next nCols
        Next nKeeps

        Dim ws As Worksheet
        Dim rng As Range

        Set ws = Worksheets("Sheet1")
        Set rng = ws.Range("A1")
        rng.Resize(nKeeps - 1, nCols - 1) = ar

    End Sub