Excel VBA 运行时错误“13”类型不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8885506/
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 Run-time error '13' Type mismatch
提问by Diogo
I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' Type mismatch
我为文件创建了一个宏，首先它运行良好，但今天我已经打开并重新启动文件和宏数百次，但总是出现以下错误：Excel VBA 运行时错误“13”类型错配
I didn't change anything in the macro and don't know why am I getting the error. Furthermore it takes ages to update the macro every time I put it running (the macro has to run about 9000 rows).
我没有更改宏中的任何内容，也不知道为什么会出现错误。此外，每次运行时更新宏都需要很长时间（宏必须运行大约 9000 行）。
The error is in the between ** **.
错误在 ** ** 之间。
VBA:
VBA：
Sub k()
Dim x As Integer, i As Integer, a As Integer
Dim name As String
name = InputBox("Please insert the name of the sheet")
i = 1
Sheets(name).Cells(4, 58) = Sheets(name).Cells(4, 57)
x = Sheets(name).Cells(4, 57).Value
Do While Not IsEmpty(Sheets(name).Cells(i + 4, 57))
a = 0
If Sheets(name).Cells(4 + i, 57) <> x Then
If Sheets(name).Cells(4 + i, 57) <> 0 Then
If Sheets(name).Cells(4 + i, 57) = 3 Then
a = x
Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - x
x = Cells(4 + i, 57) - x
End If
**Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a**
x = Sheets(name).Cells(4 + i, 57) - a
Else
Cells(4 + i, 58) = ""
End If
Else
Cells(4 + i, 58) = ""
End If
i = i + 1
Loop
End Sub
Do you think you can help me? I'm using excel 2010 on windows 7. Thanks a lot
你觉得你能帮我吗？我在 Windows 7 上使用 excel 2010。非常感谢
回答by Devin Burke
You would get a type mismatch if Sheets(name).Cells(4 + i, 57)contains a non-numeric value. You should validate the fields before you assume they are numbers and try to subtract from them.
如果Sheets(name).Cells(4 + i, 57)包含非数字值，则会出现类型不匹配。您应该先验证这些字段，然后再假设它们是数字并尝试从中减去。
Also, you should enable Unfortunately Option Strictso you are forced to explicitly convert your variables before trying to perform type-dependent operations on them such as subtraction. That will help you identify and eliminate issues in the future, too.Option Strictis for VB.NET only. Still, you should look up best practices for explicit data type conversions in VBA.
此外，您应该启用， 不幸的Option Strict以便在尝试对变量执行依赖于类型的操作（例如减法）之前，您被迫显式转换变量。这也将有助于您在未来识别和消除问题。Option Strict是仅适用于 VB.NET。不过，您应该查找 VBA 中显式数据类型转换的最佳实践。
Update:
更新：
If you are trying to go for the quick fix of your code, however, wrap the **line and the one following it in the following condition:
但是，如果您想快速修复您的代码，请**按以下条件包装该行及其后面的行：
If IsNumeric(Sheets(name).Cells(4 + i, 57))
Sheets(name).Cells(4 + i, 58) = Sheets(name).Cells(4 + i, 57) - a
x = Sheets(name).Cells(4 + i, 57) - a
End If
Note that your xvalue may not contain its expected value in the next iteration, however.
但是请注意，您的x值可能不包含在下一次迭代中的预期值。
回答by Diogo
Thank you guys for all your help! Finally I was able to make it work perfectly thanks to a friend and also you! Here is the final code so you can also see how we solve it.
谢谢大家的帮助！最后，多亏了一个朋友和你，我才能让它完美地工作！这是最终代码，因此您也可以看到我们如何解决它。
Thanks again!
再次感谢！
Option Explicit
Sub k()
Dim x As Integer, i As Integer, a As Integer
Dim name As String
'name = InputBox("Please insert the name of the sheet")
i = 1
name = "Reserva"
Sheets(name).Cells(4, 57) = Sheets(name).Cells(4, 56)
On Error GoTo fim
x = Sheets(name).Cells(4, 56).Value
Application.Calculation = xlCalculationManual
Do While Not IsEmpty(Sheets(name).Cells(i + 4, 56))
a = 0
If Sheets(name).Cells(4 + i, 56) <> x Then
If Sheets(name).Cells(4 + i, 56) <> 0 Then
If Sheets(name).Cells(4 + i, 56) = 3 Then
a = x
Sheets(name).Cells(4 + i, 57) = Sheets(name).Cells(4 + i, 56) - x
x = Cells(4 + i, 56) - x
End If
Sheets(name).Cells(4 + i, 57) = Sheets(name).Cells(4 + i, 56) - a
x = Sheets(name).Cells(4 + i, 56) - a
Else
Cells(4 + i, 57) = ""
End If
Else
Cells(4 + i, 57) = ""
End If
i = i + 1
Loop
Application.Calculation = xlCalculationAutomatic
Exit Sub
fim:
MsgBox Err.Description
Application.Calculation = xlCalculationAutomatic
End Sub
回答by cssyphus
For future readers:
对于未来的读者：
This function was abending in Run-time error '13': Type mismatch
此功能在 Run-time error '13': Type mismatch
Function fnIsNumber(Value) As Boolean
fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)")
End Function
In my case, the function was failing when it ran into a #DIV/0!or N/Avalue.
就我而言，该函数在遇到 a #DIV/0!orN/A值时失败。
To solve it, I had to do this:
为了解决它，我不得不这样做：
Function fnIsNumber(Value) As Boolean
If CStr(Value) = "Error 2007" Then '<===== This is the important line
fnIsNumber = False
Else
fnIsNumber = Evaluate("ISNUMBER(0+""" & Value & """)")
End If
End Function
回答by Siddharth Rout
Diogo
迪奥戈
Justin has given you some very fine tips :)
贾斯汀给了你一些非常好的提示:)
You will also get that error if the cell where you are performing the calculation has an error resulting from a formula.
如果您执行计算的单元格因公式而出现错误，您也会收到该错误。
For example if Cell A1 has #DIV/0! error then you will get "Excel VBA Run-time error '13' Type mismatch" when performing this code
例如，如果单元格 A1 有 #DIV/0! 错误，那么在执行此代码时，您将收到“Excel VBA 运行时错误‘13’类型不匹配”
Sheets("Sheet1").Range("A1").Value - 1
I have made some slight changes to your code. Could you please test it for me? Copy the code with the line numbers as I have deliberately put them there.
我对您的代码做了一些细微的更改。你能帮我测试一下吗？复制带有行号的代码，因为我故意将它们放在那里。
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim x As Integer, i As Integer, a As Integer, y As Integer
Dim name As String
Dim lastRow As Long
10 On Error GoTo Whoa
20 Application.ScreenUpdating = False
30 name = InputBox("Please insert the name of the sheet")
40 If Len(Trim(name)) = 0 Then Exit Sub
50 Set ws = Sheets(name)
60 With ws
70 If Not IsError(.Range("BE4").Value) Then
80 x = Val(.Range("BE4").Value)
90 Else
100 MsgBox "Please check the value of cell BE4. It seems to have an error"
110 GoTo LetsContinue
120 End If
130 .Range("BF4").Value = x
140 lastRow = .Range("BE" & Rows.Count).End(xlUp).Row
150 For i = 5 To lastRow
160 If IsError(.Range("BE" & i)) Then
170 MsgBox "Please check the value of cell BE" & i & ". It seems to have an error"
180 GoTo LetsContinue
190 End If
200 a = 0: y = Val(.Range("BE" & i))
210 If y <> x Then
220 If y <> 0 Then
230 If y = 3 Then
240 a = x
250 .Range("BF" & i) = Val(.Range("BE" & i)) - x
260 x = Val(.Range("BE" & i)) - x
270 End If
280 .Range("BF" & i) = Val(.Range("BE" & i)) - a
290 x = Val(.Range("BE" & i)) - a
300 Else
310 .Range("BF" & i).ClearContents
320 End If
330 Else
340 .Range("BF" & i).ClearContents
350 End If
360 Next i
370 End With
LetsContinue:
380 Application.ScreenUpdating = True
390 Exit Sub
Whoa:
400 MsgBox "Error Description :" & Err.Description & vbNewLine & _
"Error at line : " & Erl
410 Resume LetsContinue
End Sub
回答by Youbi
I had the same problem as you mentioned here above and my code was doing great all day yesterday.
我遇到了与您在上面提到的相同的问题，我的代码昨天一整天都运行良好。
I kept on programming this morning and when I opened my application (my file with an Auto_Open sub), I got the Run-time error '13' Type mismatch, I went on the web to find answers, I tried a lot of things, modifications and at one point I remembered that I read somewhere about "Ghost" data that stays in a cell even if we don't see it.
今天早上我继续编程，当我打开我的应用程序（我的文件带有 Auto_Open 子文件）时，我收到了运行时错误“13”类型不匹配，我在网上寻找答案，我尝试了很多东西，修改，有一次我记得我在某处读到过关于“幽灵”数据的内容，即使我们没有看到它也会留在一个单元格中。
My code do only data transfer from one file I opened previously to another and Sum it. My code stopped at the third SheetTab (So it went right for the 2 previous SheetTab where the same code went without stopping) with the Type mismatch message. And it does that every time at the same SheetTab when I restart my code.
我的代码只将数据从我之前打开的一个文件传输到另一个文件并对其求和。我的代码在第三个 SheetTab 处停止（因此它适用于前 2 个 SheetTab，其中相同的代码没有停止）并显示类型不匹配消息。当我重新启动代码时，它每次都在同一个 SheetTab 上执行此操作。
So I selected the cell where it stopped, manually entered 0,00 (Because the Type mismatch comes from a Summation variables declared in a DIM as Double) and copied that cell in all the subsequent cells where the same problem occurred. It solved the problem. Never had the message again. Nothing to do with my code but the "Ghost" or data from the past. It is like when you want to use the Control+End and Excel takes you where you had data once and deleted it. Had to "Save" and close the file when you wanted to use the Control+End to make sure Excel pointed you to the right cell.
所以我选择了它停止的单元格，手动输入 0,00（因为类型不匹配来自在 DIM 中声明为 Double 的总和变量）并在发生相同问题的所有后续单元格中复制该单元格。它解决了这个问题。再也没有收到消息。与我的代码无关，但与过去的“幽灵”或数据无关。就像当您想使用 Control+End 时，Excel 会将您带到曾经有数据的地方并将其删除。当您想使用 Control+End 确保 Excel 将您指向正确的单元格时，必须“保存”并关闭文件。
回答by chetan dubey
Sub HighlightSpecificValue()
'PURPOSE: Highlight all cells containing a specified values
Dim fnd As String, FirstFound As String
Dim FoundCell As Range, rng As Range
Dim myRange As Range, LastCell As Range
'What value do you want to find?
fnd = InputBox("I want to hightlight cells containing...", "Highlight")
'End Macro if Cancel Button is Clicked or no Text is Entered
If fnd = vbNullString Then Exit Sub
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
enter code here
Set FoundCell = myRange.Find(what:=fnd, after:=LastCell)
'Test to see if anything was found
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
Else
GoTo NothingFound
End If
Set rng = FoundCell
'Loop until cycled through all unique finds
Do Until FoundCell Is Nothing
'Find next cell with fnd value
Set FoundCell = myRange.FindNext(after:=FoundCell)
'Add found cell to rng range variable
Set rng = Union(rng, FoundCell)
'Test to see if cycled through to first found cell
If FoundCell.Address = FirstFound Then Exit Do
Loop
'Highlight Found cells yellow
rng.Interior.Color = RGB(255, 255, 0)
Dim fnd1 As String
fnd1 = "Rah"
'Condition highlighting
Set FoundCell = myRange.FindNext(after:=FoundCell)
If FoundCell.Value("rah") Then
rng.Interior.Color = RGB(255, 0, 0)
ElseIf FoundCell.Value("Nav") Then
rng.Interior.Color = RGB(0, 0, 255)
End If
'Report Out Message
MsgBox rng.Cells.Count & " cell(s) were found containing: " & fnd
Exit Sub
'Error Handler
NothingFound:
MsgBox "No cells containing: " & fnd & " were found in this worksheet"
End Sub
回答by gadolf
This error occurs when the input variable type is wrong. You probably have written a formula in Cells(4 + i, 57)that instead of =0, the formula = ""have used. So when running this error is displayed. Because empty string is not equal to zero.
当输入变量类型错误时会发生此错误。您可能已经在Cells(4 + i, 57)其中编写了一个公式，而不是=0，该公式= ""已使用。所以运行的时候会显示这个错误。因为空字符串不等于零。
