Searching Macro

[Guest]

Does anyone know how to create a macro that will automatically search another
worksheet for a name and then enter in a third item once it finds the name.

For example: Search entire worksheet 1 for "John" then enter in the date
that John's name is under?

Is this possible? HELP PLEASE!!!

 

[David McRitchie]

Are you actually saying that "John" could appear in ANY cell and if
you get a hit you want the value of the cell above that cell. Or is
only one column to be searched.

 

[Guest]

I am actually saying that "John" could appear in ANY cell and if I get a hit
I want the value of the cell above "John". So is it possible? Thanks.

 

[Dave Peterson]

It's possible, but you didn't give many details.

This may get you started...

I assumed that you wanted to look for John on Sheet1. Case didn't matter. It
was in a cell by itself. You wanted to find the first occurrence. And you
wanted to copy the cell above that "john" cell in A1 of Sheet2.



Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim WhatToLookFor As String
Dim destCell As Range

Set wks = Worksheets("Sheet1")
WhatToLookFor = "John"

Set destCell = Worksheets("sheet2").Range("a1")

With wks
Set FoundCell = .Cells.Find(what:=WhatToLookFor, _
after:=.Cells(.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox WhatToLookFor & " wasn't found on: " & wks.Name
Else
If FoundCell.Row = 1 Then
MsgBox WhatToLookFor & " was found in Row 1--nothing above!"
Else
'.offset(-1,0) is one row up in the same column
FoundCell.Offset(-1, 0).Copy _
Destination:=destCell
End If
End If
End With
End Sub