☰

当值匹配时选择单元格

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba
当值匹配时选择单元格

下面的代码会查找我在工作表 1 中的“K2”中输入的任何值，然后在工作表 2 中查找相同的值。我希望当它找到匹配项时，选择该特定单元格。有人能帮我吗？

Sub Macro()
    Dim WS As Worksheet
    Dim rSearchRng As Range
    Dim EndNum As Long
    Dim vFindVar As Variant`

    Set WS = ActiveWorkbook.Worksheets("Sheet2")

    EndNum = WS.Range("A1").End(xlDown).Row

    Set rSearchRng = WS.Range("A1:A" & CStr(EndNum))
    Set vFindVar = rSearchRng.Find(Range("K2").Value)
    If Not vFindVar Is Nothing Then
        MsgBox "Match"
    Else
        MsgBox "No Match Found"
    End If
End Sub

答案1

Sub Macro()
Dim WS As Worksheet
Dim rSearchRng As Range
Dim EndNum As Long
Dim vFindVar As Variant`
Dim wsOrig as worksheet

Set wsOrig as activesheet
Set WS = ActiveWorkbook.Worksheets("Sheet2")

EndNum = WS.Range("A1").End(xlDown).Row

Set rSearchRng = WS.Range("A1:A" & CStr(EndNum))
Set vFindVar = rSearchRng.Find(wsOrig.Range("K2").Value)
If Not vFindVar Is Nothing Then
    'to select a cell the sheet must be active
    WS.Activate
    vFindVar.Select
Else
    MsgBox "No Match Found"
End If

End Sub

相关内容