Find string in one worksheet and select it in another

Question

I've got Workbook where I got names and hours worked of employees. I'm looking for comparing rows in one worksheet (Range B6:CC6) and find it in another with selection on cell with employee name (Range A1:A5000) when I change sheets from 1 to 2.

Tried some Range.Find and others, no idea how to do it

Public Sub FindPosition()

    Dim Actcol As Integer, Pos As Range, Name As Range

    Actcol = ActiveCell.Column
    MsgBox "ActiveCell is" & Actcol
    Set Pos = Cells(6, Actcol)
    MsgBox Pos

    Pos.Select

    If Worksheets("Sheet2").Activate Then

        Worksheets("Sheet2").Range("A1:AA5100").Select
        Set Name = Selection.Find(Pos, LookIn:=xlValues)

    End If

End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Oct 29 '19 at 13:10
You mention trying `.find` but have no use of it in your code. Please provide, in your post, the code you've tried... without that, we don't have a means to help you as StackOverflow is not a code-for-you service. — Cyril, Oct 29 '19 at 13:17
@Cyril 'Public Sub FindPosition()' 'Dim Actcol As Integer, Pos As Range, Name As Range Actcol = ActiveCell.Column MsgBox "ActiveCell is" & Actcol Set Pos = Cells(6, Actcol) MsgBox Pos Pos.Select If Worksheets("Sheet2").Activate Then Worksheets("Sheet2").Range("A1:AA5100").Select Set Name = Selection.Find(Pos,LookIn:=xlValues) End If End Sub — laczi, Oct 29 '19 at 13:24
@laczi as requested, please provide *in your post*... it is nearly unreadable as a comment. — Cyril, Oct 29 '19 at 13:39
Looks like you want `Pos.Value` in your `.find()`, since you dimensioned `Pos as range`. You will be looking at a line similar to: `Set Name = Worksheets("Sheet2").Range("A1:AA5100").Find(Pos.Value)` — Cyril, Oct 29 '19 at 13:54

score 0 · Answer 1 · answered Oct 29 '19 at 14:11

First, if you want to trigger some macro by activation of Sheet2, you need to handle Activate event of Sheet2. This can be done by declaring subroutine in Sheet module like this.

Private Sub Worksheet_Activate()
    'Codes you want to be run when Sheet2 is activated.
End Sub

Second, a simple way to find a cell with specific value is to use WorksheetFunction.Match. For example,

Dim SearchInRange As Range
Set SearchInRange = Range("A1:A5000")

Dim EmployeeName As Variant
EmployeeName = ... 'Actual employee name you want to search

On Error GoTo NotFound
Dim Index As Variant
Index = WorksheetFunction.Match(EmployeeName, SearchInRange, 0)
On Error GoTo 0

SearchInRange.Cells(Index).Select

GoTo Finally

NotFound:
' Handle error

Finally:

Range.Find may also work, but remember it has the side effect of changing the state of "Find and Replace" dialog box.

score 0 · Answer 2 · answered Oct 29 '19 at 15:03

This may helps you

Option Explicit

Sub test()

    Dim i As Long, LastRowA As Long, LastRowB As Long
    Dim rngSearchValues As Range, rngSearchArea As Range
    Dim ws1 As Worksheet, ws2 As Worksheet

    'Set you worksheets
    With ThisWorkbook
        'Let say in this worksheet you have the names & hours
        Set ws1 = .Worksheets("Sheet1")
        'Let say in this worksheet you have the list of names
        Set ws2 = .Worksheets("Sheet2")
    End With

    'Find the last row of the column B with the names from the sheet with names & hours
    LastRowB = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    'Find the last row of the column A with the names from the sheet with list of names
    LastRowA = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

    'Set the range where you want to check if the name appears in
    Set rngSearchArea = ws2.Range("A1:A" & LastRowA)

    'Loop the all the names from the sheet with names and hours

    For i = 6 To LastRowB

        If ws1.Range("B" & i).Value <> "" Then

            If Application.WorksheetFunction.CountIf(rngSearchArea, "=" & ws1.Range("B" & i).Value) > 0 Then
                MsgBox "Value appears"
                Exit For
            End If

        End If

    Next i

End Sub

score 0 · Answer 3 · answered Oct 30 '19 at 09:31

Oh right, I found solution. Thanks everyone for help.

    Public Sub Position()
        Dim Accol As Integer
        Dim Pos As Range
        Dim name As Range
        ActiveSheet.name = "Sheet1"
        Accol = ActiveCell.Column
        Set Pos = Cells(6, Accol)
        Worksheets("Sheet2").Activate
        Worksheets("Sheet2").Range("a1:a5000").Select
        Set name = Selection.Find(What:=Pos, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        name.Select

    End Sub

Last thing I would like to do which I cannot solve is where do I write automatically script running when I choose Sheet2?

Find string in one worksheet and select it in another

3 Answers3