Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunting me
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunting
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunting
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 " 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunt
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 " 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 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 " 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunt
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 " 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 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 " 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunt
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. 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 " 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 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 " 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunt
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" wrote in message ... 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. 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 " 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 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 " 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application-defined or object -defined error continually haunt
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" wrote in message ... 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. 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 " 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 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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming |