VBA Excel运行时错误13类型不匹配 [英] VBA Excel run time error 13 type mismatch

问题描述

我在InputBox上遇到问题.我的宏运行良好.这是我的代码所以我在Google上发现，如果CInt函数提供了无法转换为数值的文本字符串，它将返回错误，这是真的吗?或任何建议?

I have an issue with the InputBox. my macro was working well. and this is my code so i find on google that if the CInt function is supplied with a text string that cannot be converted into a numeric value, it will return the error, is it true? or any advise?

Private Sub btn_check_Click()
Application.ScreenUpdating = False

Dim a, b, x, y, z, c As Long
Dim indicator, puzzlePartner, puzzleRaw As String

a = 5
b = 2
c = 5

Do Until Cells(c, 1) = ""
     Sheet9.Cells(c, 3).Select
        Sheet9.Cells(c, 56) = Trim(CStr(Left(Sheet9.Cells(c, 3), 15)))
    c = c + 1
    Loop

a = 5

Do Until Sheet9.Cells(a, 1) = ""
    Do Until Sheet4.Cells(b, 104) = ""
        If a = 2000 Then
        c = 1
        End If
    If Sheet4.Cells(b, 9) = Sheet9.Cells(a, 9) And Sheet4.Cells(b, 27) = Sheet9.Cells(a, 27) And Sheet4.Cells(b, 26) = Sheet9.Cells(a, 26) And _
         Sheet4.Cells(b, 25) = Sheet9.Cells(a, 25) And Sheet4.Cells(b, 24) = Sheet9.Cells(a, 24) And Sheet9.Cells(a, 52) = Sheet4.Cells(b, 104) And _
         LCase(Trim(Sheet4.Cells(b, 21))) = LCase(Trim(Sheet9.Cells(a, 21))) Then
         Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "Withdrawn" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "DC" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 9)) And Sheet9.Cells(a, 15) = "PO" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf Right(Sheet9.Cells(a, 7), 3) = "(S)" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
     ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 51)) And Sheet9.Cells(a, 15) = "APP" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
    ElseIf CInt(Cells(2, 3)) <> Year(Cells(a, 12)) And Right(Cells(a, 52), 3) = "RNW" Then
        Sheet9.Cells(a, 53) = "True"
        GoTo 1
        End If
    End If
2:
    b = b + 1
    Loop
            Sheet9.Cells(a, 53) = "False"
x = CInt(Len(Sheet9.Cells(a, 52)))
            y = x - 4
            indicator = Mid(Sheet9.Cells(a, 52), y, 1)
            If Sheet9.Cells(a, 53) = "False" And indicator = "-" Then
                Sheet9.Cells(a, 53) = "Top-up"
          End If
1:

    b = 2
a = a + 1
Loop

我还注意到该错误显示在行上

I have also noticed that this error is shown on the line

     ElseIf CInt(Cells(2, 3)) <> Year(Sheet9.Cells(a, 51)) And Sheet9.Cells(a, 15) = "APP" Then

任何人都知道为什么会这样吗?

Anyone have idea why is this happening?

推荐答案

是的， CInt 方法在传递非数值时会返回运行时错误.您可以使用

Yes, the CInt method returns a Runtime error when passed a non numeric value. You can use

IsNumeric(SheetName.Cells(2, 3))

在使用 CInt 之前执行类型检查的方法. IsNumeric 会根据传递的输入类型为您返回 true 或 false .

method to perform a type check before using CInt. IsNumeric will return you true or false based on the type of input passed.

对于日期类型，可以使用 IsDate()方法来验证单元格值是否为日期类型.仅在类型检查之后，使用 Year()方法.

In case of date type you can use IsDate() method to validate that the cell value is of date type or not. Only after type check use Year() method.

这篇关于VBA Excel运行时错误13类型不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！