写一个excel的vba逻辑是检查H列的数据将单元格中 C+数字+数字+数字 后面的字符剪切并粘贴到J列

Sub CutAndPaste() Dim lastRow As Long lastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row '获取最后一行

For i = 1 To lastRow '循环遍历每一行
    If Left(Range("H" & i).Value, 1) = "C" Then '判断是否以"C"开头
        Dim str As String
        str = Range("H" & i).Value '获取单元格值
        Dim regEx As Object
        Set regEx = CreateObject("vbscript.regexp") '创建正则表达式对象
        regEx.Pattern = "(C\d{3})(.*)" '匹配"C"后面的数字和字符
        If regEx.Test(str) Then '如果匹配成功
            Dim match As Object
            Set match = regEx.Execute(str)(0) '获取匹配结果
            Range("J" & i).Value = match.SubMatches(1) '将剪切的字符粘贴到J列
            Range("H" & i).Value = match.SubMatches(0) '将剩余的字符重新赋值给H列
        End If
    End If
Next i

End Sub