使用EXCEL的VBA宏命令来批量修改文件单元格里的批注

作用说明：找到名为Sheet1的工作表里的第73行到第100行里所有包含有批注的单元格
把这些单元格的批注里的
lastCell=“AQ33” 替换成lastCell=“AQ37”（加4行）
ifArea =[“A16:A20”] 替换成ifArea =[“A20:A24”] （两个都加4行）

使用方法：点击：开发工具——VB编辑器 或者直接ALT+F11打开
点：插入——模块——把代码粘贴到编辑器里，点运行按钮即可运行

Sub 更新批注行数()
    Dim ws As Worksheet
    Dim cell As Range
    Dim commentText As String
    Dim newRowOffset As Long
    
    ' 设置要操作的工作表
    Set ws = ThisWorkbook.Sheets("Sheet1") '将“Sheet1”替换为你的工作表名称
    
    ' 设置插入新行的偏移量
    newRowOffset = 4
    
    ' 循环遍历工作表中的每个单元格
    For Each cell In ws.Rows("73:100").Cells
        ' 检查单元格是否有批注
        If Not cell.Comment Is Nothing Then
            ' 获取批注文本
            commentText = cell.Comment.text
            ' 使用正则表达式匹配批注中的行号并自动增加
            commentText = UpdateCommentText(commentText, newRowOffset)
            ' 将更新后的批注文本重新写入批注
            cell.Comment.text commentText
        End If
    Next cell
End Sub

Function UpdateCommentText(ByVal text As String, ByVal offset As Long) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    regex.Global = True
    regex.IgnoreCase = True
    regex.Pattern = "lastCell=""[A-Z]+(\d+)"""
    
    Dim matches As Object
    Set matches = regex.Execute(text)
    
    If matches.Count > 0 Then
        Dim match As Object
        For Each match In matches
            Dim oldRow As Long
            oldRow = CLng(match.SubMatches(0))
            Dim newRow As Long
            newRow = oldRow + offset
            text = Replace(text, "lastCell=""AQ" & oldRow & """", "lastCell=""AQ" & newRow & """")
        Next match
    End If
    
    ' 更新 ifArea 的行号
    regex.Pattern = "ifArea =\[""A(\d+):AQ(\d+)""\]"
    Set matches = regex.Execute(text)
    
    If matches.Count > 0 Then
        For Each match In matches
            Dim oldStartRow As Long
            oldStartRow = CLng(match.SubMatches(0))
            Dim oldEndRow As Long
            oldEndRow = CLng(match.SubMatches(1))
            Dim newStartRow As Long
            newStartRow = oldStartRow + offset
            Dim newEndRow As Long
            newEndRow = oldEndRow + offset
            text = Replace(text, "ifArea =[""A" & oldStartRow & ":AQ" & oldEndRow & """]", "ifArea =[""A" & newStartRow & ":AQ" & newEndRow & """]")
        Next match
    End If
    
    Set regex = Nothing
    Set matches = Nothing
    
    UpdateCommentText = text
End Function