Resize method fails for 1-D array?

[quartz]

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


---
Regards,
Norman


"quartz" <quartz@discussions.microsoft.com> wrote in message
news:B45CD5ED-4A37-4F27-B74C-03809569FBEC@microsoft.com...
>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.

[Alan Beban]

quartz wrote:
> 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

[quartz]

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

"Norman Jones" wrote:

> Hi Quartz,
>
> Try:
>
> msSheetName.Range("A1"). _
> Resize(UBound(mvaFileName) - _
> LBound(mvaFileName) + 1) = mvaFileName
>
>
> ---
> Regards,
> Norman
>
>
> "quartz" <quartz@discussions.microsoft.com> wrote in message
> news:B45CD5ED-4A37-4F27-B74C-03809569FBEC@microsoft.com...
> >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.
>
>
>

[quartz]

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.

"Alan Beban" wrote:

> quartz wrote:
> > 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



"quartz" <quartz@discussions.microsoft.com> wrote in message
news:9C501714-D529-4DDA-B06B-0C22AD8447B5@microsoft.com...
> Thanks Norman, but now I get only the first array element repeated for
> every
> line...any further suggestions?
>
> "Norman Jones" wrote:
>
>> Hi Quartz,
>>
>> Try:
>>
>> msSheetName.Range("A1"). _
>> Resize(UBound(mvaFileName) - _
>> LBound(mvaFileName) + 1) = mvaFileName
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>> "quartz" <quartz@discussions.microsoft.com> wrote in message
>> news:B45CD5ED-4A37-4F27-B74C-03809569FBEC@microsoft.com...
>> >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.
>>
>>
>>

[quartz]

Thanks!

"Norman Jones" wrote:

> Hi Quartz,
>
> Try:
>
> msSheetName.Range("A1"). _
> Resize(UBound(mvaFileName) - _
> LBound(mvaFileName) + 1) = _
> Application.Transpose(mvaFileName)
>
>
> ---
> Regards,
> Norman
>
>
>
> "quartz" <quartz@discussions.microsoft.com> wrote in message
> news:9C501714-D529-4DDA-B06B-0C22AD8447B5@microsoft.com...
> > Thanks Norman, but now I get only the first array element repeated for
> > every
> > line...any further suggestions?
> >
> > "Norman Jones" wrote:
> >
> >> Hi Quartz,
> >>
> >> Try:
> >>
> >> msSheetName.Range("A1"). _
> >> Resize(UBound(mvaFileName) - _
> >> LBound(mvaFileName) + 1) = mvaFileName
> >>
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >> "quartz" <quartz@discussions.microsoft.com> wrote in message
> >> news:B45CD5ED-4A37-4F27-B74C-03809569FBEC@microsoft.com...
> >> >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.
> >>
> >>
> >>
>
>
>