Application-defined or object -defined error continually haunting me

[brentfidler]

I am using Excel 2003, Win XP Pro SP2

I have code that reads in an array from a worksheet Stage1(), processes
the array and then writes it again back to the same worksheet.

The read is happening correctly. But I get the application-defined or
object-defined error when trying to write the array to the worksheet.

Sub ReadStage1()
With Workbooks("WIPBOM.xls").Sheets("Stage1")
Stage1Count = .Range(.Range("A2"),
..Range("A2").End(xlDown)).Rows.Count - 1
Stage1Cols = .Range(.Range("A2"),
..Range("A2").End(xlToRight)).Columns.Count

'Set new array variables
ReDim Stage1(0 To Stage1Count, 1 To Stage1Cols)

'Read in Styles to array variable
For LoopStage1 = 0 To Stage1Count
For LoopCols = 1 To Stage1Cols
Stage1(LoopStage1, LoopCols) =
..Range("A2").Offset(LoopStage1, LoopCols - 1).Value
Next LoopCols
Next LoopStage1

End With

Code here manipulates variables in the array- code is too long to post.
Going into the next sub, Stage1Count=2638 and Stage1Cols=74 and array
is fully populated.

Sub Write_Stage_1
With Workbooks("WIPBOM.xls").Sheets("Stage1")
.Range("A2:IV60000").ClearContents
.Range("A2").Resize(Stage1Count, Stage1Cols).Formula =
Stage1()
End With
End Sub

I get an application-defined or object-defined error on the
..Range("A2").Resize(Stage1Count, Stage1Cols).Formula = Stage1(). It
executes the previous Clear Contents line line without error.

I have struggled with this type error for as long as I have been
teaching myself coding. I really need to find what I am doing
incorrectly.

Thanks,

Brent

 

[Guest]

I suspect your array contains something that is an invalid formula.

Why not assign it to Value unless it really contains formulas.

But just as an example, this works fine for me.

Sub EFG()
Dim i As Long, j As Long
Dim ii As Long, jj As Long
Dim v(1 To 10, 1 To 3)
For i = 1 To 10
For j = 1 To 3
v(i, j) = i * j
Next
Next
ii = Int(Rnd() * 20 + 1)
jj = Int(Rnd() * 6 + 1)
Debug.Print ii, jj
Range("A1").Resize(ii, jj).Value = v
End Sub

 

[brentfidler]

Thanks for the suggestion.

My array does not contain formulas. Nevertheless, I changed the line
..Range("A2").Resize(Stage1Count, Stage1Cols).Formula = Stage1() to
..Range("A2").Resize(Stage1Count, Stage1Cols).Value = Stage1()and I
still get the same error.

This code was working perfectly yesterday. I am not sure what has
changed in my workbook.

Any other suggestions? What other troubleshooting technique can I try?

Thank you,

Brent

 

[brentfidler]

I get the same error if I try to select a cell on the worksheet that I
want to write my array:

Sub Write_Stage_1
Workbooks("WIPBOM.xls").Sheets("Stage1").Range("A1").Select

' With Workbooks("WIPBOM.xls").Sheets("Stage1")
' .Range("A2:IV60000").ClearContents
' .Range("A2").Resize(Stage1Count, Stage1Cols).Formula =
Stage1()
' End With
End Sub

 

[Guest]

well we see that with the values you say you have, this isn't problematic:

Stage1Count=2638
Stage1Cols=74
? Range("A2").Resize(Stage1count,Stage1Cols).Address
$A$2:$BV$2639


So that would lead me to believe that Stage1count > 65535 or Stage1Cols > 256


When I do this:
Stage1Count=65536
Stage1Cols=74
? Range("A2").Resize(Stage1count,Stage1Cols).Address

I get the error you describe (as an illustration).

 

[brentfidler]

I break the code before the execution of the code to write the array.
The immediate window as well as the watch window shows that
Stage1Count=2638 and Stage1Cols=74.

So I am confident that Stage1Count > 65536.

I hope this might be of use to troubleshoot:

This code works:
Sub WriteStage1()
With Workbooks("WIPBOM.xls").Sheets("Stage1")
.Range("A2:IV60000").ClearContents
End With
End Sub

This code gives me the application-defined error:
Sub WriteStage1()
With Workbooks("WIPBOM.xls").Sheets("Stage1")
.Range("A2").Select
End With
End Sub

What else can be causing this?

 

[Dave Peterson]

You can only select a range on a sheet that's active.

You could use:

application.goto workbooks("wipbom.xls").sheets("stage1").range("a2")

And eliminate the .select problem.

 

[Tom Ogilvy]

Just some added,

Of course Brent only tried to select as a debugging approach - at least in
the code he showed. So if he makes that successful, not sure it contributes
to the original problem, but who knows.

--
Regards,
Tom Ogilvy

 

[Dave Peterson]

I didn't revisit the earlier posts. So it's very possible that my response adds
nothing toward the solution of the original problem.