Sub CopyCell()
Dim cell As Range
Set cell = FindCell("SALES", Sheets("Sheet1").Cells()
'this will search the entire sheet until it finds the first match
'if you have a smaller range replace -- .Cells() -- with say
..Range("A1:E100")
If cell Is Nothing Then
'action to take of no match
Else
cell.Copy Destination:=cell.offset(0,2)
End If
End Sub

"scott" wrote:

I'm trying to find a cell with a string of "SALES" and then copy it 2
columns over. Below is my FindCell function and CopyCell sub. I think
FindCell is ok, but CopyCell sub isn't coping the cell. Any ideas?

Sub CopyCell()
Dim cell As Range
Set cell = FindCell("SALES", Sheets(1).Cells)
If cell Is Nothing Then
'action to take of no match
Else
cell.Offset(0, 2).Copy Worksheets("Sheet1").Range("AT4")
End If
End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

Application.DisplayAlerts = False
With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

End With
End Function