对于具有VBA变量的动态范围，“运行时错误13”。数据类型不匹配

I am receiving a 'runtime error 13' for a line that is supposed to select a dynamic range from a worksheet in a opened workbook. I commented the line in the program.

我收到一行“运行时错误13”,该行应该从打开的工作簿中的工作表中选择动态范围。我评论了该计划中的这一行。

From research, I found that the 13 error is caused by a mismatch in data, so I tried changing my "v1, v2, v3..." variables to integers(they were Dim As Long).This had no effect.

从研究中我发现13错误是由数据不匹配引起的,所以我尝试将我的“v1,v2,v3 ......”变量改为整数(它们是Dim As Long)。这没有效果。

I specify a workbook and worksheet for each cell and range because I was receiving a '1004 error' before, & the answer from another thread was to qualify the ranges() & cells().

我为每个单元格和范围指定了工作簿和工作表,因为我之前收到了“1004错误”,而另一个主题的答案是限定范围()和单元格()。

What is causing this error? Could there be an issue with the workbook and worksheet datatypes? Any help is appreciated. Thanks!

导致此错误的原因是什么?是否存在工作簿和工作表数据类型的问题?任何帮助表示赞赏。谢谢!

Sub FillPartNumRev()
Dim ws As Worksheet, wb As Workbook
Dim v1 As Range, v2 As Range, v3 As Range
Dim r1 As Long, r2 As Long, r3 As Long, r4 As Long, r5 As Long
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long
For x = 1 To 5000

Set wb = Workbooks.Open(Filename:="IPIC_DATA.xlsx")

r1 = 7: c1 = 0
r2 = 7: c2 = 1
r3 = 7: c3 = 2
r4 = 7: c4 = 0
r5 = 7: c5 = 1


Set v1 = wb.Worksheets("Sheet1").Range(wb.Worksheets("Sheet1").Cells(r1, c1), wb.Worksheets("Sheet1").Cells(r2, c2))
'run-time error 13
Set v2 = wb.Worksheets("Sheet1").Range(wb.Worksheets("Sheet1").Cells(r3, c3))
Set v3 = wb.Worksheets("Sheet1").Range(wb.Worksheets("Sheet1").Cells(r1, c1))
Set v4 = wb.Worksheets("Sheet1").Range(wb.Worksheets("Sheet1").Cells(r4, c4), wb.Worksheets("Sheet1").Cells(r5, c5))

    If v4 <> "" & v2 = "" Then
        v1.Copy v4

    ElseIf v4 = "" Then
        r1 = r1 + 7
        r2 = r2 + 7

    End If

r4 = r4 + 1
c4 = c4 + 1
r5 = r5 + 1
c5 = c5 + 1
r3 = r3 + 1
c3 = c3 + 1


Next x


End Sub

2 个解决方案

#1

Kudos to @Jeeped for sorting this out - however a type mismatch error is very common when working with Excel cells in VBA. This answer addresses the more common cause, for which your now-working code still isn't immune to.

感谢@Jeeped对此进行排序 - 但是在VBA中使用Excel单元格时,类型不匹配错误非常常见。这个答案解决了更常见的原因,您现在正在使用的代码仍然无法免疫。

A type mismatch error is typically something you get when you assign a cell's value to some typed variable, be it a String, a Double, a Date, or whatever - like this:

当您将单元格的值分配给某个类型变量时,通常会出现类型不匹配错误,无论是String,Double,Date还是其他 - 如下所示:

Dim v As String
v = sheet.Range("A1").Value

It can also happen when a cell's value is involved in an expression, like this:

当单元格的值涉及表达式时也会发生这种情况,如下所示:

If sheet.Range("A1").Value <> "" Then

Why is that?

这是为什么?

If the cell is empty, or actually contains a usable value, there's no problem. However if the cell contains an Error value, such as #N/A, #VALUE!, or any other cell error, then the above code throws run-time error 13 as you're experiencing.

如果单元格为空,或实际包含可用值,则没有问题。但是,如果单元格包含错误值,例如#N / A,#VALUE!或任何其他单元格错误,则上述代码会在您遇到时抛出运行时错误13。

A cell's value is a Variant; a Variant can contain anything, including a String, an Object reference, or an Error value. VBA will implicitly convert a Variant/Date to a Variant/String if you're reading a cell value into some String variable, or if you're comparing it to some string literal.

单元格的值是变体; Variant可以包含任何内容,包括String,Object引用或Error值。如果您将单元格值读入某个String变量,或者将其与某些字符串文字进行比较,VBA将隐式将Variant / Date转换为Variant / String。

But a Variant/Error can't be implicitly (or explicitly) converted to anything.

但Variant / Error不能隐式(或显式)转换为任何东西。

So the morale of the story, is that you can never assume what the Variant subtype of a cell is going to be, nor that this subtype can be coerced into a specific data type.

因此,故事的士气是,你永远不能假设一个单元格的Variant子类型,也不能将这个子类型强制转换为特定的数据类型。

To avoid type mismatch errors when working with cells, you need to read the cell values into a Variant:

要在使用单元格时避免类型不匹配错误,您需要将单元格值读入Variant:

Dim v As Variant
v = sheet.Range("A1").Value

And then use the IsError function to determine whether you're looking at a Variant/Error:

然后使用IsError函数确定您是否正在查看Variant / Error:

If Not IsError(v) Then
    'v is safe to convert to whatever you need it to be
Else
    'cell contains an error value
End If

#2

Put Set wb = Workbooks.Open(Filename:="IPIC_DATA.xlsx") above and outside the loop. You are trying to open it 5000 times.

将Set wb = Workbooks.Open(Filename:=“IPIC_DATA.xlsx”)放在循环的上方和外部。你试图打开它5000次。
Workbooks(wb) should be wb. You've set the workbook object to a workbook type var. No need to use the object to identify wb out of the workbooks collection.

工作簿(wb)应该是wb。您已将工作簿对象设置为工作簿类型var。无需使用该对象来识别工作簿集合中的wb。
Go back to Long. Integers aren't cool.

回到龙。整数并不酷。

Here are some corrected range and cell references.

以下是一些更正的范围和单元格引用。

'I made a guess as to the folder location of IPIC_DATA.xlsx
'don't rely on default folder locations; be as specific as possible
Set wb = Workbooks.Open(Filename:=thisworkbook.path & "\IPIC_DATA.xlsx")

For x = 1 To 5000

    r1 = 7: c1 = 0  'there is no column 0
    r2 = 7: c2 = 1
    r3 = 7: c3 = 2
    r4 = 7: c4 = 0 'there is no column 0
    r5 = 7: c5 = 1

    with wb.Worksheets("Sheet1")
        Set v1 = .Range(.Cells(r1, c1), .Cells(r2, c2))
        Set v2 = .Cells(r3, c3)
        Set v3 = .Cells(r1, c1)
        Set v4 = .Range(.Cells(r4, c4), .Cells(r5, c5))
    end with

    '...
Next x

#1