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

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


--
Regards,
Tom Ogilvy


"(E-Mail Removed)" wrote:

> 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
>
>

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

Tom Ogilvy wrote:
> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >

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


(E-Mail Removed) wrote:
> 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
>
> Tom Ogilvy wrote:
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > 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
> > >
> > >

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).

--
Regards,
Tom Ogilvy



"(E-Mail Removed)" wrote:

> 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
>
>
> (E-Mail Removed) wrote:
> > 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
> >
> > Tom Ogilvy wrote:
> > > 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
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > 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
> > > >
> > > >
>
>

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?


Tom Ogilvy wrote:
> 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).
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "(E-Mail Removed)" wrote:
>
> > 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
> >
> >
> > (E-Mail Removed) wrote:
> > > 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
> > >
> > > Tom Ogilvy wrote:
> > > > 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
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "(E-Mail Removed)" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> >
> >

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.

(E-Mail Removed) wrote:
>
> 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?
>
> Tom Ogilvy wrote:
> > 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).
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "(E-Mail Removed)" wrote:
> >
> > > 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
> > >
> > >
> > > (E-Mail Removed) wrote:
> > > > 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
> > > >
> > > > Tom Ogilvy wrote:
> > > > > 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
> > > > >
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "(E-Mail Removed)" wrote:
> > > > >
> > > > > > 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
> > > > > >
> > > > > >
> > >
> > >

--

Dave Peterson

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> (E-Mail Removed) wrote:
>>
>> 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?
>>
>> Tom Ogilvy wrote:
>> > 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).
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "(E-Mail Removed)" wrote:
>> >
>> > > 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
>> > >
>> > >
>> > > (E-Mail Removed) wrote:
>> > > > 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
>> > > >
>> > > > Tom Ogilvy wrote:
>> > > > > 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
>> > > > >
>> > > > >
>> > > > > --
>> > > > > Regards,
>> > > > > Tom Ogilvy
>> > > > >
>> > > > >
>> > > > > "(E-Mail Removed)" wrote:
>> > > > >
>> > > > > > 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
>> > > > > >
>> > > > > >
>> > >
>> > >
>
> --
>
> 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.

Tom Ogilvy wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> > (E-Mail Removed) wrote:
> >>
> >> 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?
> >>
> >> Tom Ogilvy wrote:
> >> > 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).
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> >
> >> > "(E-Mail Removed)" wrote:
> >> >
> >> > > 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
> >> > >
> >> > >
> >> > > (E-Mail Removed) wrote:
> >> > > > 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
> >> > > >
> >> > > > Tom Ogilvy wrote:
> >> > > > > 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
> >> > > > >
> >> > > > >
> >> > > > > --
> >> > > > > Regards,
> >> > > > > Tom Ogilvy
> >> > > > >
> >> > > > >
> >> > > > > "(E-Mail Removed)" wrote:
> >> > > > >
> >> > > > > > 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
> >> > > > > >
> >> > > > > >
> >> > >
> >> > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson