Error trying to write array to range
 

I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

Error trying to write array to range
 

try


Sub Cap()
Dim Rng As Range
Dim Arr(1 To 5)
Set Rng = Range("A2", Cells(2, 5))
Arr(1) = "AB"
Arr(2) = "CD"
Arr(3) = "EF"
Arr(4) = "GH"
Arr(5) = "IJ"
Rng.Value = Arr
End Sub

"J. Caplan" wrote:

I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

Error trying to write array to range
 

Thanks, but that did not make a difference. I still get the error.

"Office_Novice" wrote:

try


Sub Cap()
Dim Rng As Range
Dim Arr(1 To 5)
Set Rng = Range("A2", Cells(2, 5))
Arr(1) = "AB"
Arr(2) = "CD"
Arr(3) = "EF"
Arr(4) = "GH"
Arr(5) = "IJ"
Rng.Value = Arr
End Sub

"J. Caplan" wrote:

I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

Error trying to write array to range
 

I don't get an error with your code... at least not #1004. I do get an error
#0 shown, but that is because you did not include an Exit Sub statement in
front of the ErrorHandler label, so your successful code is falling through
to the error handler below.

--
Rick (MVP - Excel)


"J. Caplan" wrote in message
...
I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

Error trying to write array to range
 

It worked ok in my brand new test worksheet. But I would add an "exit sub"
before the errorhandler: line.

Is your worksheet protected?

J. Caplan wrote:

I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

--

Dave Peterson

Error trying to write array to range
 

I realized I was missing the End Sub after I submitted, but since that wasn't
the problem, I didn't bother re-posting.

I was about to reply that my worksheet is not protected, and I realized what
the problem is. I wrote this UDF but had not put in hooks to call it yet, so
I altered the code to a formula I wrote and had it call my function. I am
failing to write to the cells since I am in the middle of a formula when I am
trying to write these values.

If I call my function from a button click, it writes fine.

Thanks for the reply and sorry for jumping the gun and posting when it was
clearly "User Error"!!!

"Dave Peterson" wrote:

It worked ok in my brand new test worksheet. But I would add an "exit sub"
before the errorhandler: line.

Is your worksheet protected?

J. Caplan wrote:

I have data in a two dimensional array of Variants but I get an
"Application-defined or Object-defined error" (number 1004) when I try to
assign to the array to the range. I tried to simplify this with a
one-dimensional array of strings and that still fails. Here is my simple
test below

Public Sub MyTest()

Dim arr() As String
ReDim arr(1 To 5)

On Error GoTo ErrorHandler

arr(1) = "AB"
arr(2) = "CD"
arr(3) = "EF"
arr(4) = "GH"
arr(5) = "IJ"

Dim rng As Range
Set rng = Range("A2", Cells(2, 5))
Debug.Print (rng.Address) ' <== Returns $A$2:$E$2

rng.value = arr ' <== ERROR OCCURS ON THIS LINE

ErrorHandler:
Call MsgBox(Err.Description & " - " & Err.Number)
End Sub

What am I missing?

--

Dave Peterson