CODEWITHSUNDEEP
excelvbamismatch
Das Bruno
Das Bruno

Reputation: 355

Excel VBA Type 13 Mismatch using Array containing values and strings

I'm having trouble with a Runtime Error 13 "Type mismatch error". I am trying to take multiple lines of code and condense them into one line that is each row placed side by side. The problem is that my inputs are strings and numbers, which is what I believe is causing this problem. How can I fix this?

Sub multRowsTo1Row()
    Dim inputRange As Variant
    Dim outputRange As Variant

    inputRange = Selection
    y = UBound(inputRange, 1)
    x = UBound(inputRange, 2)

    ReDim outputRange(1 To x * y)

    For j = 1 To y
        For i = 1 To x
            outputRange(i + y(j - 1)) = inputRange(j, i)
        Next i
    Next j

    Selection.Offset(0, x).Select


End Sub

Upvotes: 0

Views: 10309

Answers (2)

Das Bruno
Das Bruno

Reputation: 355

I was successful thanks to David. Here's my finalized code.

Sub multRowsTo1Row()

'' This takes a multiple line array and places each row side by side '' Currently places it next to the top row. This can be changed.

Dim inputRange As Variant
Dim outputRange As Variant
Dim x#, y#

inputRange = Selection

y = UBound(inputRange, 1)
x = UBound(inputRange, 2)

ReDim outputRange(1 To x * y)

For j = 1 To y
    For i = 1 To x
        outputRange(i + x * (j - 1)) = inputRange(j, i)
    Next i
Next j

''Change this if you want to output somewhere else. This pastes the output to the right side of the last entry in the first row.

    Selection.Offset(0, x).Resize(1, x * y).Select
    Selection = outputRange
End Sub

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

Declare your variables Dim x#, y# correctly. That will fix your Mismatch error, but will present you with another error, since y(j - 1) expects an array. Add the multiplication sign so that it is y * (j-1) and you will avoid that error, but you may get an overflow in the event that Selection.Rows.Count > 2, so you might also want to add a check for that.

Sub multRowsTo1Row()


    Dim inputRange As Variant
    Dim outputRange As Variant
    Dim y#, x#

    If selection.Rows.Count > 2 Then 
         MsgBox "Invalid Selection!", vbCritical
         Exit Sub
    End If

    inputRange = Selection
    y = UBound(inputRange, 1)
    x = UBound(inputRange, 2)

    ReDim outputRange(1 To x * y)

For j = 1 To y
    For i = 1 To x
        outputRange(i + y * (j - 1)) = inputRange(j, i)
    Next i
Next j

Selection.Offset(0, x).Select


End Sub

As always, much pain & troubleshooting can be avoided with use of Option Explicit and also dimensioning your variables to a specific Type :)

Upvotes: 3

Related Questions