在 VBA 中出现“错误：类型不匹配”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4222823/
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
Getting "error: type mismatch" in VBA
提问by Carlos
Am getting Run-time error 13. Type Mismatch. But i cant figure out why. Any help?
正在收到运行时错误13。类型不匹配。但我不知道为什么。有什么帮助吗？
Sub Separate_with_OR_without_comission()
Dim I As Integer
Dim WRng As Range
Dim NoRng As Range
Dim NameRgn As Range
Dim TotalCRng As Range
Set WRng = Range("with_comission")
Set NoRng = Range("without_comission")
Set NameRgn = Range("total_comission_name")
Set TotalCRng = Range("ttotal_comission")
For I = 1 To NameRgn.Rows.Count
If TotalCRng.Rows(I) > 0 Then // ERROR HERE
WRng.Rows(I) = NameRgn.Rows(I)
End If
If TotalCRng.Rows(I) < 1 Then // AND HERE
NoRng.Rows(I) = NameRgn.Rows(I)
End If
Next I
End Sub
When i try to use other test cells is fine, the problem is with those.... but they are numbers inside "ttotal_comission" why does VBA takes it as something else?
当我尝试使用其他测试单元时很好，问题在于那些……但它们是“ttotal_comission”中的数字，为什么 VBA 将其视为其他东西？
回答by Nick Spreitzer
The problem is that Rows(I)is returning a range object, not an integer value. You should fully qualify your statements like this: TotalCRng.Rows(I).Cells(1, 1).Valueor possibly TotalCRng.Cells(1, 1).Value. Written as it is, Excel will return the value from Rows(I) if it happens to be a single cell, in which case the range's value property is called, but otherwise will raise the Type Mismatch error you're seeing because you're attempting to compare a range to an integer.
问题是Rows(I)返回一个范围对象，而不是一个整数值。你应该像这样完全限定你的陈述：TotalCRng.Rows(I).Cells(1, 1).Value或者可能TotalCRng.Cells(1, 1).Value。按原样编写，如果碰巧是单个单元格，Excel 将返回来自 Rows(I) 的值，在这种情况下，范围的 value 属性被调用，否则会引发您看到的类型不匹配错误，因为您是尝试将范围与整数进行比较。
Example:
例子：
'no error
Debug.Print Sheet1.Range("B1")
'type mismatch error
Debug.Print Sheet1.Range("B1:B12")
Also, bear in mind that only the top left cell of a merged range will actually return a value.
另外，请记住，只有合并范围的左上角单元格才会实际返回一个值。
回答by Patrick Honorez
You could use use a construct like this:
您可以使用这样的构造：
for each c in range("rangeName1")
'if the source range is 3 columns to the right, same row'
c = c.offset(0,3).value
next c
回答by Bobsickle
If you are trying to check the value of the cells in each row, you need to loop through the cells and compare the values individually.
如果您尝试检查每行中单元格的值，则需要遍历单元格并单独比较这些值。
If the ranges are just single columns, instead of looping through each row, you can loop through each cell for the same effect.
如果范围只是单列，而不是遍历每一行，您可以遍历每个单元格以获得相同的效果。
For I = 1 To NameRgn.Rows.Count
For j = 1 to NameRgn.rows(I).cells.count
If TotalCRng.Rows(I).cells(j).value > 0 Then // ERROR HERE
WRng.Rows(I).cells(j) = NameRgn.Rows(I).cells(j)
End If
If TotalCRng.Rows(I).cells(j).value < 1 Then // AND HERE
NoRng.Rows(I).cells(j) = NameRgn.Rows(I).cells(j)
End If
Next j
Next I
