current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

UPDATE:

I had two problems that contributed to this error. First, I had a clash with a SAS add-in that was messing up the copy/paste functionality. Second, I had a null value in the array that was causing the transposition to fail. I have handled the null and everything works fine.

I'm trying to assign an array of values to my Excel worksheet. When I use the TRANSPOSE function I am getting type mismatch errors. I don't understand why, there are only about 200 "rows" in the set:

Worksheets("xyz").Range("A2").Resize(ValidCode, 5).Value = & _ 
  Application.WorksheetFunction.Transpose(CodeData)

It works fine when I put a static value instead of the transposed array. I don't understand what's happening to it. It is type "Variante/Variante(0 to 4, 0 to 205)"

The array needs to be transposed because I had to redim preserve it in a code loop (so the "columns" are rows, etc.) I guess I could manually transpose it, but that seems unnecessary.

share|improve this question
    
The & character is not necessary. What Type is ValidCode and what is its value? –  David Zemens Jul 18 '13 at 16:39

1 Answer 1

up vote 0 down vote accepted

The & character is not necessary and in fact raises a 1004 error when I try to implement your code.

Otherwise, I cannot replicate the error you describe, this example code works with or without Transpose function, although you need to use Transpose to achieve the desired results.

Sub Test()

Dim ws As Worksheet: Set ws = Sheets("xyz")
Dim rng As Range: Set rng = ws.Range("A2")
Dim r As Integer
Dim c As Integer
Dim CodeData() As Variant

ReDim Preserve CodeData(4, 205)   '## Create a dummy array to test this method
For r = 0 To UBound(CodeData, 1)  '## populate the array with random numbers
    For c = 0 To UBound(CodeData, 2)
        CodeData(r, c) = Application.WorksheetFunction.RandBetween(0, 100)
    Next
Next

'## Drop this array in to the worksheet:
rng.Resize(UBound(CodeData, 2) + 1, UBound(CodeData, 1) + 1).Value = _
    Application.WorksheetFunction.Transpose(CodeData)


End Sub
share|improve this answer
    
Yeah, it works fine on mine too. The problem is happening in the Application.WorksheetFunction.Transpose(CodeData) part. Can you think of anything in the array itself that could cause this? –  Jeffrey Kramer Jul 18 '13 at 17:12
    
It's a "Run-time error '13' Type Mismatch that pops up. –  Jeffrey Kramer Jul 18 '13 at 17:14
    
It also pastes the data just fine if I don't transpose (though the rows/columns are of course inverted.) So weird. I guess I can just paste, cut, transpose or something. –  Jeffrey Kramer Jul 18 '13 at 17:17
    
Does my code work fine on your computer? Or does it raise the same error? –  David Zemens Jul 18 '13 at 17:22
    
Okay the problem isn't in the code, I just realized that the PASTE SPECIAL is completely greyed out anytime I open Excel. I think something else is going on beyond just this issue. –  Jeffrey Kramer Jul 18 '13 at 17:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.