如何通过Excel VBA基于自动生成工作表SheetN的列数据在PrincipalSheet指定列批量查找非NAN内容并替换为空？

我来帮你搞定这个VBA代码的问题！首先咱们先拆解你原代码里的几个核心问题，再一步步写出能完美实现需求的完整代码。

原代码里的明显错误 #

• 变量初始化混乱：你先把i设为1，马上又改成20，而且max变量完全没赋值，导致循环根本不会执行
• 语法错误：IsNot IsEmpty是错误写法，正确的应该是Not IsEmpty
• 单元格引用搞反：你要遍历SheetN的B列（第2列）的行，应该用Cells(i, 2)，而不是Cells(2, i)（后者是在第2行遍历列）
• 未定义目标工作表：NewSheet变量既没定义也没赋值，VBA根本不知道你指的是哪个工作表

完整实现代码 #

下面是能实现你需求的完整VBA代码，我会逐段解释关键逻辑：

Sub RemoveUsersFromPrincipalSheet()
    Dim wsPrincipal As Worksheet
    Dim wsSheetN As Worksheet
    Dim lastRowSheetN As Long
    Dim i As Long
    Dim userToRemove As String
    Dim rngPrincipal As Range
    Dim cell As Range
    
    ' 1. 绑定主工作表PrincipalSheet
    Set wsPrincipal = ThisWorkbook.Sheets("PrincipalSheet")
    
    ' 2. 绑定自动生成的SheetN（这里假设它是工作簿里最后一个工作表）
    ' 如果SheetN不是最后一个，后面有补充的查找逻辑
    Set wsSheetN = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    ' 3. 获取SheetN中B列的最后一行，避免遍历空行
    lastRowSheetN = wsSheetN.Cells(wsSheetN.Rows.Count, "B").End(xlUp).Row
    
    ' 4. 遍历SheetN的B列所有非空、非NAN的用户
    For i = 1 To lastRowSheetN
        userToRemove = Trim(wsSheetN.Cells(i, "B").Value)
        
        ' 跳过空单元格和字符串类型的NAN（如果是公式返回的#N/A，用IsError判断）
        If userToRemove <> "" And UCase(userToRemove) <> "NAN" Then
            ' 遍历PrincipalSheet的R列所有有内容的单元格
            Set rngPrincipal = wsPrincipal.Range("R1:R" & wsPrincipal.Cells(wsPrincipal.Rows.Count, "R").End(xlUp).Row)
            For Each cell In rngPrincipal
                ' 处理用户在不同位置的情况：开头、中间、结尾
                cell.Value = Replace(cell.Value, " " & userToRemove & "; ", "; ")
                cell.Value = Replace(cell.Value, userToRemove & "; ", "")
                cell.Value = Replace(cell.Value, " " & userToRemove, "")
                cell.Value = Replace(cell.Value, userToRemove, "")
            Next cell
        End If
    Next i
    
    ' 5. 清理R列中多余的分号（开头、结尾或连续分号）
    Set rngPrincipal = wsPrincipal.Range("R1:R" & wsPrincipal.Cells(wsPrincipal.Rows.Count, "R").End(xlUp).Row)
    For Each cell In rngPrincipal
        ' 去掉开头的分号和空格
        Do While Left(cell.Value, 2) = "; " Or Left(cell.Value, 1) = ";"
            cell.Value = Mid(cell.Value, 2)
        Loop
        ' 去掉结尾的分号和空格
        Do While Right(cell.Value, 2) = "; " Or Right(cell.Value, 1) = ";"
            cell.Value = Left(cell.Value, Len(cell.Value) - 1)
        Loop
        ' 替换连续分号为单个分号
        cell.Value = Replace(cell.Value, ";;", ";")
        cell.Value = Replace(cell.Value, "; ;", ";")
    Next cell
    
    MsgBox "用户清理完成！"
End Sub

关键逻辑补充说明 #

1. 如果SheetN不是最后一个工作表：可以用下面的代码替换绑定wsSheetN的部分，自动找到编号最大的SheetN：

Dim ws As Worksheet
Dim sheetNum As Long
Dim maxSheetNum As Long
maxSheetNum = 0
For Each ws In ThisWorkbook.Sheets
    If ws.Name Like "Sheet#" Then
        sheetNum = Val(Mid(ws.Name, 5))
        If sheetNum > maxSheetNum Then
            maxSheetNum = sheetNum
            Set wsSheetN = ws
        End If
    End If
Next ws

2. 处理公式返回的#N/A错误：如果SheetN的B列是公式返回的#N/A，把判断非NAN的代码改成：

If Not IsError(wsSheetN.Cells(i, "B").Value) And userToRemove <> "" Then

内容的提问来源于stack exchange，提问作者Miguel V