Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
Hi Quartz,
Try: msSheetName.Range("A1"). _ Resize(UBound(mvaFileName) - _ LBound(mvaFileName) + 1) = mvaFileName --- Regards, Norman "quartz" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
Hi Quartz,
Try: msSheetName.Range("A1"). _ Resize(UBound(mvaFileName) - _ LBound(mvaFileName) + 1) = _ Application.Transpose(mvaFileName) --- Regards, Norman "quartz" wrote in message ... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize method fails for 1-D array?
Thanks!
"Norman Jones" wrote: Hi Quartz, Try: msSheetName.Range("A1"). _ Resize(UBound(mvaFileName) - _ LBound(mvaFileName) + 1) = _ Application.Transpose(mvaFileName) --- Regards, Norman "quartz" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why Copy/Paste fails using Offset & Resize of myRange? | Excel Discussion (Misc queries) | |||
Paste method fails in VBA | Excel Programming | |||
Workbooks.open method fails | Excel Programming | |||
Copy method fails in IIS | Excel Programming | |||
resize method | Excel Programming |