run time error 1004 when transposing array to range

[cg85cg]

Here is the trouble line

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

Similar lines have worked in other programs

When this line is executed:
mostup is a 2 dimensional array (1 to 64350, 1 to 31)
The array is being transposed over existing data on the worksheet

The range should be the same size as the array because of the Ubounds when defining the cells. I do not know where to go from here. I can provide more code if necessary.

 

[SkipVought]

Hi,

"mostup is a 2 dimensional array (1 to 64350, 1 to 31)
The array is being transposed over existing data on the worksheet"

It that's the case, then the transpose would be 31 rows by 64359 columns.

Do you see a problem in that? I do.

Skip,

_[red][/red]

 

[cg85cg]

i am confused. I thought that when declaring a 2D array the format is this:

DIM mostup(rows,columns)

 

[SkipVought]

True. But transpose SWITCHES rows & columns.

Skip,

_[red][/red]

 

[cg85cg]

huh...well that's interesting. how should i fix this problem? i could write a loop that would switch the rows and columns before I transpose, but i'm sure there is a more elegant solution.

 

[SkipVought]

"...could write a loop that would switch the rows and columns before I transpose...

Are we talking the same language?

TRANSPOSE means to SWITCH rows & columns.

By TRANSPOSE, do you mean getting the array into the worksheet?

Skip,

_[red][/red]

 

[cg85cg]

hmm...yes that is what i mean. I have never run into a problem where my rows and columns have been switched by the transpose funtion, there was nothing to suggest that transpose meant to switch them. i found the transpose function when searching online for how to put the contents of an arraty into a range. is there a better way to transfer the data?

 

[SkipVought]

"...there was nothing to suggest that transpose meant to switch them..."

TRANSPOSE

Returns a vertical range of cells as a horizontal range, or vice versa.

Skip,

_[red][/red]

 

[SkipVought]

"...I have never run into a problem where my rows and columns have been switched by the transpose funtion..."

There would not be a problem if you had less than 256 rows.

"is there a better way to transfer the data?"

EXACTLY what is your objective? Please use the term, TRANSPOSE, correctly. Please be painfully detailed, as I cannot see within your head. Please be CLEAR, CONCISE & COMPLETE.

Skip,

_[red][/red]

 

[xlbo]

do you have your OPTION BASE set to 1 or 0 in your module?

I have had issues before when option base 0 (default) is used as ubound will return 1 less than you imagine as the array index starts from 0 rather than 1

try entering

Option Base 1

as the 1st line in your module and see if that does the trick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cg85cg]

yes option base one is true.

i have an array that i am putting values into from other arrays. this array needs to be transfered to a worksheet. the range of cells to which the array is being transfered will be defined in size by the array itself by using UBound(array). the line that originally gave the error message is as follows:

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

where mostup is a 2D array (1 to 64350, 1 to 31)

 

[SkipVought]

Well your code worked for me.

Skip,

_[red][/red]