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

Having a ReDim statement directly before loading an array to a range seems very fishy to me. If the array is not loaded with anything you cannot load the array to a range. — Scott Holtzman
– Scott Holtzman, Commented Sep 7, 2017 at 19:19
In your second example you're not using dest when you assign the value from the array. Any merged cells, sheet protection? — Tim Williams
– Tim Williams, Commented Sep 7, 2017 at 19:21
@ScottHoltzman - I have a couple loops in between there preparing and loading the data, but rest assured it's not empty that I'm trying to load it. I can post the intermediate code if it may help but I left it out initially for clarity. — Nitin
– Nitin, Commented Sep 7, 2017 at 20:07
@TimWilliams My mistake, I copied over the wrong line. I'll fix that - thank you! — Nitin
– Nitin, Commented Sep 7, 2017 at 20:07
FWIW - The Value becoming value is usually caused by having some variable somewhere within your project that has a name of value. The VBE then tries to preserve case whenever it sees it used. — YowE3K
– YowE3K, Commented Sep 7, 2017 at 20:11

Felix · Accepted Answer · 2017-09-07 19:40:03Z

3

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

answered Sep 7, 2017 at 19:40

Felix

1,8682 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nitin Over a year ago

Thanks - I ran that exact program and it worked... however I still can't get my main program to work. This at least can give me a starting point for debugging.

Nitin Over a year ago

I finally figured out my issue - my array had some strings that started with an equals sign, in some cases. I only wish VBA had more verbose error messages.

Collectives™ on Stack Overflow

VBA Transferring array to a sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related