如何判断Excel工作表列中是否存在指定值并调整VBA新增成员逻辑

Got it, let's adjust your existing VBA code to add the member existence check you need. The key here is to first search the target worksheet for the member's name before deciding whether to insert a new row or handle the existing entry.

Here's the revised code with detailed comments:

With Worksheets("Enroll Course")
    Dim targetSheet As Worksheet
    Dim memberName As String
    Dim findResult As Range
    
    ' Grab the target worksheet and member name from your source ranges
    Set targetSheet = ThisWorkbook.Worksheets(.Range("C6").Value)
    memberName = .Range("C5").Value
    
    ' Search column A of the target sheet for an exact match of the member name
    Set findResult = targetSheet.Range("A:A").Find( _
        What:=memberName, _
        LookAt:=xlWhole, ' Ensures full cell match (no partial hits like "John" vs "Johnny")
        MatchCase:=False, ' Toggle to True if you need case-sensitive matching
        SearchFormat:=False)
    
    ' Only insert the row and copy the name if the member isn't found
    If findResult Is Nothing Then
        targetSheet.Rows(8).Insert Shift:=xlShiftDown
        .Range("C5").Copy targetSheet.Range("A8")
    Else
        ' Member already exists - add your custom logic here!
        ' Example: MsgBox "Member " & memberName & " is already in row " & findResult.Row
        ' Or: Update existing data, highlight the row, etc.
    End If
End With

Quick Tips: #

• Exact vs Partial Match: Swap xlWhole to xlPart if you want to catch partial name matches (use with caution to avoid false positives).
• Case Sensitivity: Flip MatchCase to True if you need to differentiate between names like "Anna" and "anna".
• Existing Member Actions: The Else block is where you'll build out the "查找该成员...的内容" functionality—use findResult.Row to reference the existing entry's row for any updates or checks.

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