使用EXCEL中编辑VBA代码实现条件检索批量一键替换

请确保您已经按照以下步骤运行代码：

打开Excel并创建一个新的工作簿或打开一个已有的工作簿。
按下Alt + F11键，打开VBA编辑器。
在VBA编辑器中，点击菜单栏的插入，然后选择模块，在模块中粘贴上述代码。
按下F5键运行代码，或者点击菜单栏的运行，然后选择运行子/用户窗体。

如果仍然没有改变，请尝试以下操作：

确保您的表格中包含处理人列和专业列。
检查代码中的单元格引用是否正确。例如，如果您的表格从第3行开始，则应将代码中的B2:B" & ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row更改为B3:B" & ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row（假设处理人列是第3列）。
检查代码中的字符串是否匹配。例如，如果您要替换的字符串是”浮云”，则代码是正确的。如果您要替换的字符串是”%”，则需要将代码中的cell.Value = "浮云"更改为cell.Value = "%"。

Sub ReplaceName()
    Dim rng As Range
    Dim cell As Range

    Set rng = ActiveSheet.Range("J2:J" & ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row)

    For Each cell In rng
        If cell.Value = "浮云" Then
            cell.Value = "谢林"
            cell.Offset(0, -8).Value = "综维"
        End If
    Next cell
End Sub

解释当（J列）中出现”浮云”时，自动将其替换为”谢林”，并在此行对应的（B列）中填写为”综维”。由于执行代码检索条件在第10列所以-8为B列

当有多个值时复制中间段稍作修改即可

Sub ReplaceName()
    Dim rng As Range
    Dim cell As Range

    Set rng = ActiveSheet.Range("J2:J" & ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row)

    For Each cell In rng
        If cell.Value = "浮云" Then
            cell.Value = "谢林"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "%" Then
            cell.Value = "杨俞"
            cell.Offset(0, -8).Value = "弱电"
        ElseIf cell.Value = "人在路上" Then
            cell.Value = "蒋桂林"
            cell.Offset(0, -8).Value = "消防"
        ElseIf cell.Value = "." Then
            cell.Value = "赵小飞"
            cell.Offset(0, -8).Value = "电梯"
        End If
    Next cell
End Sub

同时修改时间列+专业列+处理人列

Sub ReplaceName()
    Dim rng As Range
    Dim cell As Range

    Set rng = ActiveSheet.Range("J2:J" & ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row)

    For Each cell In rng
        If cell.Value = "浮云" Then
            cell.Value = "程刚"

同时修改时间列格式+填充专业列+处理人列+报修人列提取


Sub ReplaceName()
    Dim rng As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.Worksheets("Table") '根据实际情况修改工作表名称[提取H列文字并覆盖H列]
    Set rng = ws.Range("H1:H" & ws.Cells(ws.Rows.Count, "H").End(xlUp).Row)

    For Each cell In rng
        result = ""
        For i = 1 To Len(cell.Value)
            If IsNumeric(Mid(cell.Value, i, 1)) Then
                result = Mid(cell.Value, 1, i - 1)
                Exit For
            End If
        Next i
        cell.Value = result
    Next cell


    Set rng = ActiveSheet.Range("J2:J" & ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Row)

    For Each cell In rng
        If cell.Value = "浮云" Then
            cell.Value = "程刚"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "%" Then
            cell.Value = "杨俞"
            cell.Offset(0, -8).Value = "弱电"
        ElseIf cell.Value = "人在路上" Then
            cell.Value = "蒋桂林"
            cell.Offset(0, -8).Value = "消防"
        ElseIf cell.Value = "." Then
            cell.Value = "赵小飞"
            cell.Offset(0, -8).Value = "电梯"
        ElseIf cell.Value = "" Then
            cell.Value = "田茂盛"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "皮蛋" Then
            cell.Value = "陈强"
            cell.Offset(0, -8).Value = "暖通"
        ElseIf cell.Value = "唐" Then
            cell.Value = "唐恭勘"
            cell.Offset(0, -8).Value = "强电"
        ElseIf cell.Value = "史迪仔" Then
            cell.Value = "张宇"
            cell.Offset(0, -8).Value = "暖通"
        ElseIf cell.Value = "许东" Then
            cell.Value = "许东"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "赵子龙来也" Then
            cell.Value = "赵志龙"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "氛" Then
            cell.Value = "蒋鹏"
            cell.Offset(0, -8).Value = "强电"
        ElseIf cell.Value = "春去秋又来" Then
            cell.Value = "黄光泽"
            cell.Offset(0, -8).Value = "强电"
        ElseIf cell.Value = "裂冰" Then
            cell.Value = "李琥"
            cell.Offset(0, -8).Value = "暖通"
        ElseIf cell.Value = "為" Then
            cell.Value = "唐光超"
            cell.Offset(0, -8).Value = "强电"
        ElseIf cell.Value = "阿立、" Then
            cell.Value = "刘立斌"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "牛奶不纯" Then
            cell.Value = "谢林"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "牛奶不纯" Then
            cell.Value = "谢林"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "养乐多" Then
            cell.Value = "姚金鹏"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "廖福荣" Then
            cell.Value = "廖福荣"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "阿杜" Then
            cell.Value = "杜小军"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "李堂洪" Then
            cell.Value = "李堂洪"
            cell.Offset(0, -8).Value = "综维"
        ElseIf cell.Value = "白茶清欢无别事" Then
            cell.Value = "杨双全"
            cell.Offset(0, -8).Value = "综维"
        End If
    Next cell

    Set ws = ThisWorkbook.Worksheets("Table") '根据实际情况修改工作表名称
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    '将E列中的时间格式从"2023/12/31 14:59:30"更改为"14:59"这样的格式
    For i = 2 To lastRow
        ws.Cells(i, "E").NumberFormat = "hh:mm"
    Next i

    '将F列和G列中的内容合并，并将合并后的内容覆盖到G列
    For i = 2 To lastRow
        ws.Cells(i, "G").Value = ws.Cells(i, "F").Value & " " & ws.Cells(i, "G").Value
    Next i

    '清除F列内容
    For i = 2 To lastRow
        ws.Cells(i, "F").ClearContents
    Next i
End Sub

点击前往导出工单查询系统 (wolife.club)

发送评论 编辑评论

发送评论编辑评论