☰

Excel：VBA 计算在“空白”单元格上出错

tag-icon • tag-icon microsoft-excel vba runtime-error
Excel：VBA 计算在“空白”单元格上出错

当我使用“原始计算余额”按钮运行工作表“$$$”中的 L 列的 VBA 计算时，它给出“运行时错误 13：类型不匹配”。这令人困惑，因为这多年来一直没有问题，直到我更改了工作表“模板”中 C 列的公式，使没有数据的行保持“空白” ""。

电子表格在此： XLS 文件

我已确定的是：""当我使用 VBA 执行简单的加法计算时，用创建并复制到另一张表的空白单元格会导致运行时错误。Excel 将这些“空白”单元格视为文本，从而破坏了加法公式。

重复错误的步骤如下：

1) 工作表“模板”C 列上的公式导致单元格为“空白” ""，如预期的那样。这是自动的。公式为：

  =IF(B4="","",IFERROR(INDEX($F$3:$F$8,MATCH(B4,$E$3:$E$8,0)),""))

2) 在工作表“$$$”上，按“插入模板”按钮。这会从工作表“模板”复制范围“Template_RecurringExp”，并将其粘贴到工作表“$$$”中最后使用的行后的 IK 列中。我在模板中保留空白行，以便于在模板行之间输入手动数据。

3) 在“$$$”表上按“原始计算余额”。这将根据上面行中的值和 K 列中的值计算余额。此操作的 VBA（位于“SumBalanceOriginal”模块中）为：

    Option Explicit
Sub RunningBalO()
'creates a running balance column
'assumes starting bal is the first cell of bal col
'assumes +/- transactions are 1 column to the left of the bal col & 1 row below starting bal
Application.ScreenUpdating = False
Dim NumRows As Integer
Dim colDebit As Integer
Dim tempSum As Single
Dim i As Integer
Dim ws As Worksheet
Dim cStartBal As Range
Dim RowStart As Integer
Dim colBal As Integer
Dim coldebitltr As String

Set ws = ThisWorkbook.Sheets("$$$")
Set cStartBal = ws.Range("l4")                'L4

tempSum = cStartBal.Value               'starting balance for the PENDING transactions
colDebit = cStartBal.Column - 1
colBal = cStartBal.Column
RowStart = cStartBal.Row + 1              'First row with debits, used to set "i" in loop


'Find last used row in debit column
coldebitltr = Split(Cells(1, colDebit).Address, "$")(1)  'Converts the column # to a letter to pass to func LastRow
        'Debug.Print "col letter for debits= " & coldebitltr
NumRows = LastRow(coldebitltr, ws) + 1      'call func LastRow for "K" and Suntrust

        'Debug.Print "NumRows= " & NumRows
'Exit Sub
        'Debug.Print "col# for Debit= " & colDebit
       'Debug.Print "Rowstart= " & RowStart
'Calculate & fill running balance
With ws
    For i = RowStart To NumRows             '"i = " starting row of debits
          tempSum = tempSum - ws.Cells(i, colDebit).Value
            ' Debug.Print "sum = " & tempSum
            'Debug.Print "i = " & i
            ws.Cells(i, colBal).Value = Format(tempSum, "0.00")
       Next i
End With
Application.ScreenUpdating = True
End Sub

运行过程中发生错误tempSum = tempSum - ws.Cells(i, colDebit).Value。

我能想到的所有行动方案是：

1) 找到一种方法来删除“空白”单元格，并将其变成没有 的空单元格""。我还没有在网上找到任何可以做到这一点的方法。有些方法可以使用NA()而不是""，但看起来行不通。

2) 通过“插入模板”按钮执行的“InsertTemplate”宏进行粘贴时，将 K 列中的“空白”单元格转换为空单元格。不确定如何执行此操作。

3) 编辑 VBA 公式以忽略“空白”文本单元格。
我在模块“SumBalanceRevised”中的计算之前添加了此 if 语句：

If IsNumeric(ws.Cells(i, colDebit).Value) Then tempSum = tempSum - ws.Cells(i, colDebit).Value

它由名为“修订的计算余额”的按钮执行。这会导致计算跳过空行。看起来计算是正确的。我希望它不会在余额列中留下空单元格，而是完全填满该列，就像在 M 列（测试列）中一样。我不清楚如何做到这一点。

4) 将 VBA 添加到“计算余额”VBA，以将 K 列中的“空白”单元格转换为空单元格或非文本。

对于如何使 COA 发挥作用或未列出的替代方案的任何建议，我们都表示感谢。

答案1

移动此 VBA 行：

 ws.Cells(i, colBal).Value = Format(tempSum, "0.00")

在 L 列的结果之外IF/End IF进行填写，直到最后使用的行。

这样就解决了 COA 3 中的问题。

现在开始尝试获取正确的格式。

答案2

您的问题是您试图在数学方程中使用零长度字符串或类似数字的文本。6 = 6 - ""或者6 = 6 - "3"和一样错误6 = 6 - "A"。

运行时错误 13：类型不匹配

如果您没有手动更改单元格的默认水平对齐方式，您会注意到任何文本（无论它是否看起来像数字）都是左对齐的；真正的数字和日期默认是右对齐的。您可以使用此方法解决当前的问题。

tempSum = tempSum - Val(.Cells(i, colDebit).Value)

但还存在许多其他问题。最明显的是这个。

ws.Cells(i, colBal).Value = Format(tempSum, "0.00")

这将获取您刚刚生成的干净数字并将其转换为看起来像数字的文本。您想要更改单元格的数字格式。

.Cells(i, colBal).NumberFormat = "0.00"

现在，您的编码风格与我自己的非常不同，您可能需要考虑这种一次性替代方案。

Option Explicit

Sub RunningBalO()
    'creates a running balance column

    With ThisWorkbook.Sheets("$$$")

        'fix the last running balance
        With .Cells(.Rows.Count, "L").End(xlUp)

            .Value = Val(.Value2)
            .NumberFormat = "0.00"

        End With

        With .Range(.Cells(.Rows.Count, "L").End(xlUp).Offset(1, 0), _
                    .Cells(.Rows.Count, "K").End(xlUp).Offset(0, 1))

            .FormulaR1C1 = "=SUM(R[-1]C, -RC[-1])"
            .NumberFormat = "0.00"
            'optionally remove formulas
            '.Value = .Value2

        End With

    End With

End Sub

相关内容