Excel VBA 类型 13 不匹配使用包含值和字符串的数组

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/16703116/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 21:15:20  来源:igfitidea点击:

Excel VBA Type 13 Mismatch using Array containing values and strings

excelvbamismatch

提问by Das Bruno

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?

我遇到了运行时错误 13“类型不匹配错误”的问题。我试图将多行代码压缩成一行,每行并排放置。问题是我的输入是字符串和数字,我认为这是导致此问题的原因。我怎样才能解决这个问题?

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

回答by David Zemens

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.

Dim x#, y#正确声明变量。这将修复您的 Mismatch 错误,但会出现另一个错误,因为y(j - 1)需要一个数组。添加乘号,这样y * (j-1)您就可以避免该错误,但如果发生该错误,您可能会遇到溢出Selection.Rows.Count > 2,因此您可能还想为此添加一个检查。

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 Explicitand also dimensioning your variables to a specific Type:)

与往常一样,通过使用Option Explicit变量并将变量调整为特定的大小,可以避免很多痛苦和故障排除Type:)

回答by Das Bruno

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