Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and Join Function
Hello,
This code fails on "Wholeline=Join(vCellArray, sSeperator)". When a two dimensional array of N rows and M columns with M being greater than 1 is passed to the routine, the function works great. When the array is N rows with only one column is passed, the function gives me a type 13 error on the line previously mentioned. Can anyone explain this? Dim objFS As Object Dim objTextFile As Object Dim sWholeLine As String Dim vRow As Variant Dim vCellArray As Variant Const ForReading = 1, ForWriting = 2, ForAppending = 8 Set objFS = CreateObject("Scripting.FileSystemObject") Set objTextFile = objFS.OpenTextFile(sFileName, ForAppending, True, 0) For Each vRow In ExportRange.Cells.Rows ' With 2-D arrays we need to get values in a horizontal 1-D array with two transposes!? (Thanks Tom Ogilvy) vCellArray = Application.Transpose(Application.Transpose(Worksh eets(vRow.Parent.Name).Range(vRow.Address).Value)) sWholeLine = Join(vCellArray, Seperator) '<----Error 13 Type Mismatch objTextFile.WriteLine sWholeLine Next It almost seems that vCellArray needs to contain more than one item. And vcellArray does contain a correct value. I'm stumped. Regards, Stefano Condotta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and Join Function
Stefano,
The "Join" function requires an array as the first argument. When ExportRange is only one column then vCellArray is returning a string not a Variant containing an array. The following appears to work... vCellArray = Application.Transpose _ (Application.Transpose(Worksheets(vRow.Parent.Name ).Range(vRow.Address).Value)) If IsArray(vCellArray) Then sWholeLine = Join(vCellArray, Separator) Else sWholeLine = vCellArray End If Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Stefano Condotta" wrote in message news:Ja2wf.152642$2k.102977@pd7tw1no... Hello, This code fails on "Wholeline=Join(vCellArray, sSeperator)". When a two dimensional array of N rows and M columns with M being greater than 1 is passed to the routine, the function works great. When the array is N rows with only one column is passed, the function gives me a type 13 error on the line previously mentioned. Can anyone explain this? Dim objFS As Object Dim objTextFile As Object Dim sWholeLine As String Dim vRow As Variant Dim vCellArray As Variant Const ForReading = 1, ForWriting = 2, ForAppending = 8 Set objFS = CreateObject("Scripting.FileSystemObject") Set objTextFile = objFS.OpenTextFile(sFileName, ForAppending, True, 0) For Each vRow In ExportRange.Cells.Rows ' With 2-D arrays we need to get values in a horizontal 1-D ' array with two transposes!? (Thanks Tom Ogilvy) vCellArray = Application.Transpose _ (Application.Transpose(Worksheets(vRow.Parent.Name ).Range(vRow.Address).Value)) sWholeLine = Join(vCellArray, Seperator) '<----Error 13 Type Mismatch objTextFile.WriteLine sWholeLine Next It almost seems that vCellArray needs to contain more than one item. And vcellArray does contain a correct value. I'm stumped. Regards, Stefano Condotta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays and Join Function
Thanks Jim,
Your idea has led me to a similar good solution. Stefano "Jim Cone" wrote in message ... Stefano, The "Join" function requires an array as the first argument. When ExportRange is only one column then vCellArray is returning a string not a Variant containing an array. The following appears to work... vCellArray = Application.Transpose _ (Application.Transpose(Worksheets(vRow.Parent.Name ).Range(vRow.Address).Value)) If IsArray(vCellArray) Then sWholeLine = Join(vCellArray, Separator) Else sWholeLine = vCellArray End If Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Stefano Condotta" wrote in message news:Ja2wf.152642$2k.102977@pd7tw1no... Hello, This code fails on "Wholeline=Join(vCellArray, sSeperator)". When a two dimensional array of N rows and M columns with M being greater than 1 is passed to the routine, the function works great. When the array is N rows with only one column is passed, the function gives me a type 13 error on the line previously mentioned. Can anyone explain this? Dim objFS As Object Dim objTextFile As Object Dim sWholeLine As String Dim vRow As Variant Dim vCellArray As Variant Const ForReading = 1, ForWriting = 2, ForAppending = 8 Set objFS = CreateObject("Scripting.FileSystemObject") Set objTextFile = objFS.OpenTextFile(sFileName, ForAppending, True, 0) For Each vRow In ExportRange.Cells.Rows ' With 2-D arrays we need to get values in a horizontal 1-D ' array with two transposes!? (Thanks Tom Ogilvy) vCellArray = Application.Transpose _ (Application.Transpose(Worksheets(vRow.Parent.Name ).Range(vRow.Address).Value)) sWholeLine = Join(vCellArray, Seperator) '<----Error 13 Type Mismatch objTextFile.WriteLine sWholeLine Next It almost seems that vCellArray needs to contain more than one item. And vcellArray does contain a correct value. I'm stumped. Regards, Stefano Condotta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Join tables like inner join in Access | Excel Discussion (Misc queries) | |||
Using arrays or range in IF function - HELP PLS | Excel Worksheet Functions | |||
Help with a UDF Function using Arrays... | Excel Programming | |||
Multiple arrays in Median function -- VBA | Excel Programming | |||
Function: Join Cells with Format | Excel Programming |