error when transfering array to range

[cg85cg]

Here is the code, all other code has been commented out. the problem lies somewhere in the following lines

Option Base 1
Sub Profitability()

Dim mostup()

ReDim mostup(1 To Workbooks("Most Up.xls").Worksheets(1).UsedRange.Rows.Count, _
1 To Workbooks("Most Up.xls").Worksheets(1).UsedRange.Columns.Count + 25)
mostup = Workbooks("Most Up.xls").Worksheets(1).UsedRange.Value

'values added to array

Workbooks("Most Up.xls").Worksheets(1).Range(Cells(1, 1), Cells(UBound(mostup, 1), UBound(mostup, 2))).Value = mostup


This program takes a range and puts it into an array, adds values to the empty spaces in the array and then replaces the old range with a new one from the array.

When the program tries to execute the last line i run into runtime error 1004 user defined or object defined error. the array mostup is 64350 by 31 and is being put into a range whose size is defined by the size of the array. I've tried everything i can think of.

Skip, you said a while ago that you had no trouble with my code, so i wonder if there is something particular about my system that is preventing this assignment from executing. just a thought.

 

[SkipVought]

Hi,

I modified your case as posted (removing only the Workbooks() reference, ant it ran to completion sucessfully.

Sub test()

    Dim mostup()
    
    ReDim mostup(1 To Worksheets(1).UsedRange.Rows.Count, _
            1 To Worksheets(1).UsedRange.Columns.Count + 25)
    mostup = Worksheets(1).UsedRange.Value
    
    Sheets(1).Cells.ClearContents
    
    Worksheets(1).Range(Cells(1, 1), Cells(UBound(mostup, 1), UBound(mostup, 2))).Value = mostup
End Sub

My sample base was 5 rows & 2 columns. Will your code run on a lesser amount of data?

Skip,

_[red][/red]