尝试分配单元格值，但不断提醒"type mismatch"

Question

感谢您的回答。我意识到我在哪里犯了错误，所以我修改了一点，但弹出了一个新的错误提醒。它不断提醒我"预期:("在"Instra = Instrb + 2"行上。

Option Explicit
Sub separate()
Dim instrb As Integer
Dim Instra As Integer
Dim i As Integer
i = 2
Do Until Worksheets(1).Cells(i, "A") = ""
instrb = InStr(Cells(i, "A").Text, "pm")
Instra = Instrb + 2
    Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A"), (Len(Worksheets(1).Cells(i, "A")) - Instra))
    i = i + 1
Loop
End Sub

---

我是编码新手，所以我知道我的工作看起来很愚蠢。我正在尝试做的是复制 A 列中"pm"右侧的所有单词并粘贴到 B 列中。但是它不断提醒我"类型不匹配"。我认为我做得对，所以不确定发生了什么。

Sub separate()
    Dim instrb As Integer
    Dim Instra As Integer
    Dim i As Integer
    i = 2
    Do Until Worksheets(1).Cells(i, "A") = ""
        instrb = InStr(Cells(i, "A").Text, "pm")
        Instra = Instrab + 2
        Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A"), Len(Worksheets(1).Cells(i, "A") - Instra) + 2).Text
        i = i + 1
    Loop
End Sub

Answer 1

考虑：

Sub separate()
    Dim instrbb As Integer
    Dim instra As Integer
    Dim i As Integer
    i = 2
    Do Until Worksheets(1).Cells(i, "A") = ""
        instrbb = InStr(Cells(i, "A").Text, "pm")
        instra = instrbb + 2
        Cells(i, "B").Value = Right(Worksheets(1).Cells(i, "A").Text, (Len(Worksheets(1).Cells(i, "A")) - instra + 1))
        i = i + 1
    Loop
End Sub

但您可以通过以下方式简化：

Sub separate()
    Dim instrbb As Integer
    Dim instra As Integer
    Dim i As Integer
    Dim s As String
    i = 2
    With Worksheets(1)
        Do Until .Cells(i, "A") = ""
            s = .Cells(i, "A").Text
            .Cells(i, "B").Value = Split(s, "pm")(1)
            i = i + 1
        Loop
    End With
End Sub

笔记：

1. 避免使用 Instrb 作为变量，因为它是预定义的 VBA 函数
2. 更正了解析字符串时的简单数学错误
3. 正如其他人所提到的，明智的做法是用Long代替Integer
4. 正如其他人所提到的，使用Option Explicit是明智的