VBA Excel VLookup

udz 发表于 Dev

乌兹

我有一个具有以下值的第一个工作表

A列
**发件人姓名** 
SAAD 
MAJID SR AL SAAD IBRAHIM BIN SABTU或ZULKIFLEE BIN ABDUL RAHMAN 
PUSPA LAL JONES 
RENJA BAHADUR
尼泊尔RENJA BAHADUR RANGER 
RENJA BAHADUR HAMAL 
PARSHU RAM KARKI

第二个工作表具有以下值

A栏
**先生的名字**
琼斯
游侠
布朗·
哈马尔·
卡尔基

如果第二张工作表中提到的姓氏作为全名的一部分出现，我想在VBA中使用VLookup查找和删除第一张工作表中的数据行。

本质上，它将留下以下记录。


SAAD MAJID SR AL SAAD IBRAHIM BIN SABTU或ZULKIFLEE BIN ABDUL RAHMAN 
RENJA BAHADUR尼泊尔

我已经在VBA中编写了以下代码，但出现错误。

Dim NameArray() As String
Dim result

Sub vlookupcode()
'Find last row with data in Column A
lastrow = Range("A" & Rows.Count).End(xlUp).row
'Start at bottom and delete rows with errors
For myNA = lastrow To 1 Step -1
    'If IsError(Cells(myNA, 1)) Then

  tmp = Cells(myNA, 1).Value
  'MsgBox tmp
  NameArray() = Split(tmp, " ")
  For i = LBound(NameArray) To UBound(NameArray)

    'MsgBox i & " " & NameArray(i)

    result = Application.VLookup(NameArray(i), Sheet2.Range("A2:A6"), 1, False)

    If IsError(result) Then
        MsgBox "Error"
        Cells(myNA, 1).EntireRow.Delete
    End If
  Next

 Next
End Sub

你能帮我解决这个问题吗？

用户3819867

Sub vlookupcode()
'Find last row with data in Column A
lastrow = Range("A" & Rows.Count).End(xlUp).Row
'the range to which you want to compare
Dim comparerng As Range
    Set comparerng = Sheet2.Range("A2:A6")
'the boolean that stores whether there were occurences
Dim result As Boolean

'Start at bottom and delete rows with no matching values in the other set
For myNA = lastrow To 1 Step -1

  tmp = Cells(myNA, 1).Value
  'there are no occurrences until found
  result = True

    For Each cell In comparerng.Cells
        If LCase(tmp) Like "*" & LCase(cell.Value2) & "*" Then result = False 'if there's a match then set the boolean to false
    Next cell

    'if there was no value found then delete
    If result Then
        ert = MsgBox("Do you want to delete " & tmp & "?", vbOKCancel) 'if you prompt then why not ask for feedback?
        If ert = vbOK Then Cells(myNA, 1).EntireRow.Delete
    End If
Next
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。