Resize method fails for 1-D array?

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.

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.
>
>
>

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.
>>
>>
>>

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.
> >>
> >>
> >>
>
>
>