Resize method fails for 1-D array?

[Guest]

I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in multiple
rows) is loaded from a custom function. This array holds a list of all the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet I get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName

The array "mvaFileName" is dimensioned at module level as a variant.

The "resize" method is the fastest and most convenient method I know to
blast data into a sheet from an array. How can I get this to function?

Thanks in advance for your assistance.

 

[Norman Jones]

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = mvaFileName

 

[Alan Beban]

I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in multiple
rows)

That is not a one-dimensional array; it is a two-dimensional array.

is loaded from a custom function. This array holds a list of all the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet I get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName
. . .

No, the error is that you need Sheets("msSheetName") instead of
Sheets(msSheetName)

Alan Beban

 

[Guest]

Thanks Norman, but now I get only the first array element repeated for every
line...any further suggestions?

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = mvaFileName


---
Regards,
Norman

I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in multiple
rows) is loaded from a custom function. This array holds a list of all the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet I
get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName

The array "mvaFileName" is dimensioned at module level as a variant.

The "resize" method is the fastest and most convenient method I know to
blast data into a sheet from an array. How can I get this to function?

Thanks in advance for your assistance.

 

[Guest]

Thanks, but that is not correct and it may not be apparent from my post, but
"msSheetName" is string variable and therefore does not take the quotes.

Thanks anyway for your post.

I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in multiple
rows)

That is not a one-dimensional array; it is a two-dimensional array.

is loaded from a custom function. This array holds a list of all the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet I get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName
. . .

No, the error is that you need Sheets("msSheetName") instead of
Sheets(msSheetName)

Alan Beban

 

[Norman Jones]

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = _
Application.Transpose(mvaFileName)


---
Regards,
Norman

Thanks Norman, but now I get only the first array element repeated for
every
line...any further suggestions?

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = mvaFileName


---
Regards,
Norman

I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in
multiple
rows) is loaded from a custom function. This array holds a list of all
the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet
I
get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName

The array "mvaFileName" is dimensioned at module level as a variant.

The "resize" method is the fastest and most convenient method I know to
blast data into a sheet from an array. How can I get this to function?

Thanks in advance for your assistance.

 

[Guest]

Thanks!

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = _
Application.Transpose(mvaFileName)


---
Regards,
Norman

Thanks Norman, but now I get only the first array element repeated for
every
line...any further suggestions?

Hi Quartz,

Try:

msSheetName.Range("A1"). _
Resize(UBound(mvaFileName) - _
LBound(mvaFileName) + 1) = mvaFileName


---
Regards,
Norman


I am using Office 2003 on Windows XP.

A one-dimensional array (i.e. the array has one column of data in
multiple
rows) is loaded from a custom function. This array holds a list of all
the
file names in a folder that the user selects.

When I try to use the "Resize" method to shotgun the data into a sheet
I
get
a "subscript out of range" error. The error appears to be in the second
"UBound" clause in the following line:

Sheets(msSheetName).[A1].Resize(UBound(mvaFileName, 1), _
UBound(mvaFileName, 2)).Value = mvaFileName

The array "mvaFileName" is dimensioned at module level as a variant.

The "resize" method is the fastest and most convenient method I know to
blast data into a sheet from an array. How can I get this to function?

Thanks in advance for your assistance.