如何找到不匹配的变量(运行时错误 13)

Question

我正在尝试为我的同事 VBA 代码找到此代码中的变量不匹配。然而，我无法找到问题所在。该代码应该根据第 3 张表中的手动输入更新 2 张不同的工作表。这是关于安全隐患的。

调试说这行代码是搞砸的那行

Previouscellcontentbefore = ActiveCell.Offset(rowbefore + 1, columbefore + 1)

完整代码：

Dim I As Integer
Dim row As Integer
Dim before As String
Dim after As String
Dim cons As String
Dim conscat As String
Dim checks As String
Dim check2 As String
Dim check3 As String
Dim rowbefore As String
Dim columbefore As String
Dim rowafter As String
Dim columafter As String
Dim checkbefore As String
Dim checkafter As String
Dim Previouscellcontentbefore As Integer
Dim Previouscellcontentafter As Integer

Sheets("for calculations").Visible = True
cons = Application.InputBox(prompt:="Personnel; Environment; Assets; Reputation; All", Title:="Choose consequence (NB: Case sensitive)", Default:="All")
Worksheets("For calculations").Activate
Range("D37:I42").ClearContents
Range("L37:Q42").ClearContents
Range("C34").ClearContents
Select Case cons
Case "All"
Range("C34").Value = "Risk matrix shows all types of consequences"
Case "Personnel"
Range("C34").Value = "Risk matrix shows all types of Personnel consequences"
Case "Environment"
Range("C34").Value = "Risk matrix shows Environmental consequences"
Case "Asset"
Range("C34").Value = "Risk matrix shows Asset consequences"
Case "Reputation"
Range("C34").Value = "Risk matrix shows Reputation consequences"
End Select

For I = 1 To 200
Range("C47").Value = Worksheets("HAZIDS").Cells(I + 5, 2).Value
conscat = Range("F47")
check2 = cons Like conscat
check3 = cons Like "All"
If cons Like "All" Then
check2 = True
End If
If check2 Then
before = Range("D47")
after = Range("E47")
rowbefore = Mid(before, 2, 1)
columbefore = Mid(before, 4, 1)
rowafter = Mid(after, 2, 1)
columafter = Mid(after, 4, 1)
checkbefore = Not rowbefore Like "" And Not columbefore Like ""
checkafter = Not rowafter Like "" And Not columafter Like ""
If checkbefore Then
Range("C36").Select
Previouscellcontentbefore = ActiveCell.Offset(CInt(rowbefore) + 1, CInt(columbefore) + 1)
ActiveCell.Offset(CInt(rowbefore) + 1, CInt(columbefore) + 1) = Range("C47").Value & ", " & Previouscellcontentbefore
If checkafter Then
Range("K36").Select
Previouscellcontentafter = ActiveCell.Offset(CInt(rowafter) + 1, CInt(columafter) + 1)
ActiveCell.Offset(CInt(rowafter) + 1, CInt(columafter) + 1) = Range("C47").Value & ", " & Previouscellcontentafter
End If
End If
End If

End Sub

我希望宏根据"HAZIDS"中的手动输入更新表"之前的风险矩阵"和"之后的风险矩阵" 然而，"用于计算"的表格似乎有问题

Answer 1

你正在做

rowbefore = Mid(before, 2, 1) 'Rownumber in matrix before
columbefore = Mid(before, 4, 1) 'Columnumber in matrix before

由于您没有声明这些变量，因此它们会得到一个字符串值。(因为Mid返回一个字符串(

之后，在调试指示的行中执行此操作：

Previouscellcontentbefore = ActiveCell.Offset(rowbefore + 1, columbefore + 1)

您正在将整数值 1 添加到字符串值，这没有意义。

您需要做的是使用option explicit并正确定义变量。 然后，您可能需要像这样使用函数 Cint 将字符串转换为整数

Previouscellcontentbefore = ActiveCell.Offset(Cint(rowbefore) + 1, Cint(columbefore) + 1)

仅当您的rowbefore和columnbefore实际包含可转换为整数的字符串时，这才有效。如果没有，您将收到错误。