Excel VBA Range.Value 类型不匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42032481/
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
Excel VBA Range.Value type mismatch?
提问by studiis
I've written code to delete a row if a cell in column G has the value " - ". However, I'm getting a type mismatch when referring to the cell in the for each loop. I know the problem is coming from "curr.Value," but I don't know how to fix it. Any ideas?
如果 G 列中的单元格具有值“ - ”，我已经编写了删除行的代码。但是，在引用 for each 循环中的单元格时，我遇到了类型不匹配的问题。我知道问题来自“curr.Value”，但我不知道如何解决。有任何想法吗？
Here's my code:
这是我的代码：
Sub deleteRows()
Dim curr As Range
Dim ws As Worksheet
Set ws = Workbooks("ExtractedColumns").Sheets("practice")
For Each curr In
Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
If curr.Value = " - " Then
EntireRow.Delete
End If
Next
End Sub
回答by Shai Rado
I'm sorry, but the reason for your errors is not what you accepted as "Answer".
对不起，您的错误的原因不是您接受的“答案”。
First, when you loop :
For Each curr In
Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
首先，当你循环时：
For Each curr In
Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
the syntax suppose to be in 1 line :
语法假设在 1 行：
For Each curr In Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
For Each curr In Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
(unless you had -between the 2 lines of code, or you didn't share your code as you have it).
（除非你有-两行代码，或者你没有分享你的代码）。
However, you already assigned Workbooks("ExtractedColumns").Sheets("practice")to wsin the previous line, so why not use
For Each curr In ws.Range("G:G").
但是，您已经在上一行中分配了Workbooks("ExtractedColumns").Sheets("practice")to ws，那么为什么不使用
For Each curr In ws.Range("G:G").
Furthermore, looping through your entire column G will take forever, loop through only occupied cells in Column G, with:
此外，循环遍历整个 G 列将花费很长时间，只循环遍历 G 列中被占用的单元格，使用：
For Each curr In ws.Range("G1:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)
For Each curr In ws.Range("G1:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)
2ns reason, once you find the line where curr.Value = " - ", and you want to delete that entire row, you need to use curr.EntireRow.Delete.
2ns 的原因，一旦找到 where 行curr.Value = " - "，并且想要删除整行，就需要使用curr.EntireRow.Delete.
And last, like mentioned by @Comintern, you need to trap cells with errors, you don't need to assign another variable to the curr.Value, just use If IsError(curr.Value) Then.
最后，就像@Comintern 提到的那样，您需要捕获有错误的单元格，您不需要为 分配另一个变量curr.Value，只需使用If IsError(curr.Value) Then.
Code
代码
Sub deleteRows()
Dim curr As Range
Dim ws As Worksheet
Set ws = Workbooks("ExtractedColumns").Sheets("practice")
' loop through only occupied cells in Column G
For Each curr In ws.Range("G1:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)
On Error Resume Next
If IsError(curr.Value) Then ' check if cell.value return an error
If Err.Number <> 0 Then
Err.Clear
On Error GoTo 0
End If
Else ' current cell.value doesn't return an error
If curr.Value = " - " Then
curr.EntireRow.Delete '<-- delete entire row
End If
End If
Next curr
End Sub
回答by PaulG
You could try to assign the Value to a variant and checking the type.
您可以尝试将 Value 分配给变体并检查类型。
Dim vVal as Variant
.....
vVal = curr.Value
if IsError(vVal) then
etc
....
回答by TheSilkCode
I suspect the type mismatch error is being caused by curr containing a numeric value, being compared to a string... hard to know for sure without being able to see the rest of the code and how curr is populated but try using curr.textwhich returns a string for your If test, instead of curr.value.
我怀疑类型不匹配错误是由包含数值的 curr 引起的，与字符串进行比较......很难确定而无法看到其余代码以及 curr 是如何填充的，但尝试使用curr.textwhich 返回一个用于 If 测试的字符串，而不是curr.value.
Hope this helps, TheSilkCode
希望这会有所帮助，TheSilkCode
