excel - Sheet2의 단어 목록에서 Sheet1의 범위 내 단어 검색 및 일치하는 경우 Sheet1의 단어 지우기

게시 된 코드에 많은 문제가 있습니다. 나는 결국 이것이 당신이 찾고있는 것이라고 생각합니다. 코드는 명확성을 위해 주석 처리되었습니다 :

Sub tgr()
    Dim wb As Workbook
    Dim wsInput As Worksheet
    Dim wsNames As Worksheet
    Dim rInputData As Range
    Dim rNameList As Range
    Dim DataCell As Range
    Dim rClear As Range
    Dim lRow As Long
    Set wb = ActiveWorkbook
    Set wsInput = wb.Sheets("InputSheet")   'Change to the actual sheet name of your input sheet
    Set wsNames = wb.Sheets("NameList")     'Change to the actual sheet name of your name list sheet
    'Get last used row of the C:H columns in wsInput
    With wsInput.Range("C:H")
        lRow = .Find("*", .Cells(1), , , , xlPrevious).Row
        If lRow < 6 Then Exit Sub   'No data
    End With
    'Use the last used row to define your inputdata range, this was hardcoded to C6:H200 in your question
    Set rInputData = wsInput.Range("C6:H" & lRow)
    'Define the namelist range using all populated cells in column E of wsNames, this was hardcoded to E2:E50 in your question
    Set rNameList = wsNames.Range("E2", wsNames.Cells(wsNames.Rows.Count, "E").End(xlUp))
    If rNameList.Row < 2 Then Exit Sub  'No data
    'Data has been found and ranges assigned
    'Now loop through every cell in rInputData
    For Each DataCell In rInputData.Cells
        'Check if the cell being looked at exists in the NameList range
        If WorksheetFunction.CountIf(rNameList, DataCell.Value) > 0 Then
            'Found to exist, add the cell to the Clear Range
            If rClear Is Nothing Then
                Set rClear = DataCell   'First matching cell added
            Else
                Set rClear = Union(rClear, DataCell)    'All subsequent matching cells added
            End If
        End If
    Next DataCell
    'Test if there were any matches and if so clear their contents
    If Not rClear Is Nothing Then rClear.ClearContents
End Sub