Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write an array to a range without looping | Excel Worksheet Functions | |||
How to write data in array to range on worksheet | Excel Programming | |||
Write from variant array into range | Excel Programming | |||
Write 3d array to range | Excel Programming | |||
write array to range | Excel Programming |