• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

writing arrays to a range and type mismatch errors

Target

Target

New Member
first post here so go easy :)

I have a routine that loops through a text file and writes a (manipulated) string into an array, then writes the array out to a range, or at least that's what it's supposed to do...

I can fill the array no problem, but when I try and write it out again I get an 'error 13, type mismatch' error.

I can loop through the array and write out the values so I know the values are there (though this isn't viable as I'm typically looking at >100K records).

I understand what the error message is implying, but I don't understand it in the context I'm getting it, ie the range should be type agnostic.

FWIW I have an equivalent routine in another macro that does work...

I've been researching on this for over a week and I haven't found anything useful, though it seems to be a common enough error, so if anyone here has any idea's I'd be most grateful
 
Target

Firstly, Welcome to the Chandoo.org Forums

Can you please post your file/s or the code at least

The Output Range must be the same 2D size as the Array
 
here's the (abbreviated) offending code...

Code: 
Dim myarray() As String, i As Long

Do
  ReDim Preserve myarray(0, i) ' As String

  Line Input #1, MyRecordIn  ' Read record.

  myarray(0, i) = Var_A & "," & Replace(Var_B, " ", "") & "," & Var_C & "," & Date & "," & Var_D & "," & Var_F & "," & Replace(Var_G, " ", "") & ",," & Trim(Left(MyRecordIn, 20)) & "," & date2 & "," & Time & "," & Trim(Mid(MyRecordIn, 35, 12)) & "," & Trim(Mid(MyRecordIn, 48, 12)) & "," & Trim(Mid(MyRecordIn, 60, 7)) & "," & Trim(Mid(MyRecordIn, 67, 8)) & "," & Trim(Right(MyRecordIn, 9))
   
  i = i + 1

Loop Until InStr(MyRecordIn, " END OF REPORT ") Or EOF(1)

Close #1  ' Close Input file.

Application.Workbooks.Add

With Range(Cells(2, 1), Cells(UBound(myarray, 2) + 1, 1))
  .Value = Application.Transpose(myarray)
End With

The vars are all strings except where other wise state, eg date/time

The source file contains proprietary info so I can't share it, but any text file should suffice with a little bit of effort

I've also played with the size of the output range and it makes no difference, though as far as I can see it shouldn't make any difference, ie if it's larger than the array it should just fill the 'empty' cells with #N/A, and if it's smaller the output would be truncated
 
Can you upload a sample file with only a few rows of data which is randomised, modified so as to be non proprietry

Also I don't see where Var_A, Var_B etc come from ?
 
Last edited:
Have you tried defining myArray as a Variant instead of a String
Code: 
Dim myarray() As Variant
 
try this...

highly redacted, but...

var_a is an alpha string
Var_b is a numeric string
var_c is a alpha string
var_d is a date value
var_e is an alpha string (derived from a string value in the source)
var_f is an alpha string (derived from a string value in the source)
var_g could be an alpha or a numeric string
Date is derived from the source ie 10Jun is changed to a date value
Time is derived from the source ie 12:38P is changed to a date value
 

Attachments

  • test_file.txt
    1.8 KB · Views: 5
Target

If I use the following code
Code: 
Sub h()
Dim myarray() As String, i As Long
Open "C:\Users\Huis\Desktop\test_file-1.txt" For Input As #1

Var_A = "HuiA"
Var_B = "12345"
Var_C = "HuiC"
Var_D = "06/08/2014"
Var_E = "HuiE"
Var_F = "HuiF"
Var_G = "HuiG"


Do
  ReDim Preserve myarray(0, i) ' As String

  Line Input #1, myRecordin  ' Read record.

  myarray(0, i) = Var_A & "," & Replace(Var_B, " ", "") & "," & Var_C & "," & Date & "," & Var_D & "," & Var_F & "," & Replace(Var_G, " ", "") & ",," & Trim(Left(myRecordin, 20)) & "," & date2 & "," & Time & "," & Trim(Mid(myRecordin, 35, 12)) & "," & Trim(Mid(myRecordin, 48, 12)) & "," & Trim(Mid(myRecordin, 60, 7)) & "," & Trim(Mid(myRecordin, 67, 8)) & "," & Trim(Right(myRecordin, 9))
  
  i = i + 1

Loop Until InStr(myRecordin, " END OF REPORT ") Or EOF(1)

Close #1  ' Close Input file.

'Application.Workbooks.Add

With Range(Cells(2, 1), Cells(UBound(myarray, 2) + 1, 1))
  .Value = Application.Transpose(myarray)
End With

End Sub

I get the following output in A2:A32

Code: 
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,Account numb,,11:56:43 AM,oice Number,Date of,issue,,of issue
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,, ,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,....Var_B...,,11:56:43 AM,.Var_B....,|07 JU,L 14|,,JUL 14|
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,|  da,te  |,,date  |
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,-----------,,11:56:43 AM,------------,------------,-------,--------,---------
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,F...........,.........,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,tring).....,,(var_G,)99 9999,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,4:30,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,6:30,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,8:30,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,1:30,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,2:15,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,2:33,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,1:05,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,2:13,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,4:01,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,2:31,0.14
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,,,11:56:43 AM,,,,,
HuiA,12345,HuiC,6/08/2014,06/08/2014,HuiF,HuiG,,999999,,11:56:43 AM,XXXXXXXXXXi,9999999999,W XYZ,12:55,0.14

Is that correct or explain what is incorrect?
 
Target said:
here's the (abbreviated) offending code...

Code: 
Dim myarray() As String, i As Long

Do
  ReDim Preserve myarray(0, i) ' As String

  Line Input #1, MyRecordIn  ' Read record.

  myarray(0, i) = Var_A & "," & Replace(Var_B, " ", "") & "," & Var_C & "," & Date & "," & Var_D & "," & Var_F & "," & Replace(Var_G, " ", "") & ",," & Trim(Left(MyRecordIn, 20)) & "," & date2 & "," & Time & "," & Trim(Mid(MyRecordIn, 35, 12)) & "," & Trim(Mid(MyRecordIn, 48, 12)) & "," & Trim(Mid(MyRecordIn, 60, 7)) & "," & Trim(Mid(MyRecordIn, 67, 8)) & "," & Trim(Right(MyRecordIn, 9))
  
  i = i + 1

Loop Until InStr(MyRecordIn, " END OF REPORT ") Or EOF(1)

Close #1  ' Close Input file.

Application.Workbooks.Add

With Range(Cells(2, 1), Cells(UBound(myarray, 2) + 1, 1))
  .Value = Application.Transpose(myarray)
End With

The vars are all strings except where other wise state, eg date/time

The source file contains proprietary info so I can't share it, but any text file should suffice with a little bit of effort

I've also played with the size of the output range and it makes no difference, though as far as I can see it shouldn't make any difference, ie if it's larger than the array it should just fill the 'empty' cells with #N/A, and if it's smaller the output would be truncated
Click to expand...

The syntax looks good but I think you are getting type mismatch error because of size of elements in your array. Could you confirm the line that causes error?

Check this link:
http://stackoverflow.com/questions/20055784/best-workaround-for-vba-transpose-array-length-limit
 
Hui said:
Target

If I use the following code
<code snipped>

I get the following output in A2:A32

Code: 
snipped

Is that correct or explain what is incorrect?
Click to expand...

the output looks correct, although not every line in the source file generates an output

and you used my code verbatim too, curiouser and curiouser...
 
shrivallabha said:
The syntax looks good but I think you are getting type mismatch error because of size of elements in your array. Could you confirm the line that causes error?

Check this link:
http://stackoverflow.com/questions/20055784/best-workaround-for-vba-transpose-array-length-limit
Click to expand...

the error occurs at the '.Value = Application.Transpose(myarray)' line.

Interesting reference to the allowable size of an array. I had considered some sort of limitation, but google seemed to indicate that most of these types of limitations had been removed (with versions 2K3 and up?)

I have another example where I split the input/output into 50K chunks (eg when the array size reaches 50K it's written out and reset) which worked OK, but wasn't a particularly elegant solution

I should also have noted in my OP that I'm using excel 2010.
 
Target said:
the error occurs at the '.Value = Application.Transpose(myarray)' line.

Interesting reference to the allowable size of an array. I had considered some sort of limitation, but google seemed to indicate that most of these types of limitations had been removed (with versions 2K3 and up?)

I have another example where I split the input/output into 50K chunks (eg when the array size reaches 50K it's written out and reset) which worked OK, but wasn't a particularly elegant solution

I should also have noted in my OP that I'm using excel 2010.
Click to expand...
I use Excel 2010 at home and the limit still seems to be the same. 65536!
 
ok, testing would seem to support that limitation, though I can't find any reference to it on the interwebs.

so I have to go with my 'other solution' and process in blocks of 65K records

FWIW the limitation only seems to apply to the transpose function, ie I can put far more than 65k records into the array without an issue, I just can't write them out again...

many thanks for your time and patience, it was much appreciated!!
 
A lot of Excel functions still have internal limits of 65536 records I assume because they either haven't been fully converted to 64 bit or to maintain backward compatibility

You may need to write it out as chucks of 65000 records
 
Yes, the limitation applies only to Transpose part.

It probably has to do with the fact that Transpose is a function which is exposed through VBA like other functions but it is a worksheetfunction.

At any given point of time in a worksheet 65536 records are not needed for transpose and they can't be transposed considering that maximum number of columns is 16384. So the capacity of 2^16 is much higher than needed capacity of 2^14.

So in theory this may not be a limitation for MS as the possibility of someone transposing array in memory (maybe?)was not part of specification for TRANSPOSE.
 

Hi !

Mega Tip to bypass Transpose function limitation is to use
an external object like, for example, a form ListBox :​
Code: 
Function NbDim%(V)
        On Error Resume Next
    Do
        U& = UBound(V, N% + 1):  If Err Then NbDim = N: Exit Do Else N = N + 1
    Loop
End Function

Function InverseArray(V)
    If TypeName(V) = "Range" Then V = V.Value

    If IsArray(V) Then
        With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
            .Column = V:  If NbDim(V) = 1 Then InverseArray = .Column Else InverseArray = .List
        End With
    End If
End Function

Enjoy it and don't hesitate to click on Like !

Edit : adding function NbDim used in InverseArray (transpose) function …​
 
Last edited:
You must log in or register to reply here.
Back
Top