时间戳

首页 > 解决方案 > 无效的过程调用或参数（错误 5）但代码正确执行

excel - 无效的过程调用或参数（错误 5）但代码正确执行

问题描述

我正在编写一个宏，它基本上会将数据从一张纸复制并粘贴到另一张纸上。代码执行并运行良好，直到最后一位我需要从指定列中的每个单元格中删除最后三个字符。

代码做得很好，但之后我总是会出错

无效的过程调用或参数（错误 5）

这是 Excel 识别错误的部分，即使它通过删除单元格值的最后三个字符来完成其工作。在更改单元格值之前，它们看起来像 000123.HK

我的代码应该在最后删除 .HK

感谢您对此的任何帮助，也很高兴获得有关如何使我的代码更高效的提示！

With Worksheets("Earnings Final")
    For i = 1 To lastRow
        .Cells(i, "B") = Left(.Cells(i, "B").Value, Len(.Cells(i, "B").Value) - 3)
    Next i
End With

完整代码如下：


Private Sub button_Click()

Dim ricRange As Range
Dim ricEveryNth As Range
Dim ricRow As Long

Dim sRange As Range
Dim sEveryNth As Range
Dim sRow As Long

Application.ScreenUpdating = False

Sheets("SheetA").Activate

With Worksheets("SheetA")
    Set ricRange = .Range("A2", Cells(.Rows.Count, "A").End(xlUp))
End With

Worksheets("SheetA").Range("A1").Cut
Worksheets("SheetA").Range("B1").Select
ActiveSheet.Paste

For ricRow = 1 To ricRange.Rows.Count Step 3
If ricRow = 1 Then
    Set ricEveryNth = ricRange(ricRow, 1)
Else
    Set ricEveryNth = Union(ricRange(ricRow, 1), ricEveryNth)
End If
Next ricRow
Application.Goto ricEveryNth

Selection.Copy
Sheets("SheetB").Activate
Worksheets("SheetB").Range("B1").PasteSpecial Paste:=xlPasteFormulas

With Worksheets("SheetA")
    Set sRange = .Range("B1", Cells(.Rows.Count, "B").End(xlUp))
End With

For sRow = 1 To sRange.Rows.Count Step 3
If sRow = 1 Then
    Set sEveryNth = sRange(sRow, 1)
Else
    Set sEveryNth = Union(sRange(sRow, 1), sEveryNth)
End If
Next sRow
Application.Goto sEveryNth

Selection.Copy
Sheets("SheetB").Activate
Worksheets("SheetB").Range("A1").PasteSpecial Paste:=xlPasteFormulas

' Remove .HK from stock codes

Dim lastRow As Long
Dim i As Integer


Sheets("SheetB").Activate
lastRow = Cells(Rows.Count, "B").End(xlUp).Row

With Worksheets("SheetB")
    For i = 1 To lastRow
        .Cells(i, "B") = Left(.Cells(i, "B").Value, Len(.Cells(i, "B").Value) - 3)
    Next i
End With

End Sub

标签： excelvba

解决方案

这可能是 Left/Len 方法失败。检查 len 是否大于 3，因为您可能会得到 Left(string, x) 其中 x 可能为负数：

With Worksheets("Earnings Final")
  For i = 1 To lastRow
    If Len(.Cells(i, "B")) >= 3 Then
      .Cells(i, "B") = Left(.Cells(i, "B").Value, Len(.Cells(i, "B").Value) - 3)
    End If
  Next i
End With

推荐阅读