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

Hi Scott,

Since Sheets(1) may or my not be the same as Sheets("Sheet1"),. it is
probably advisable to replace the forner with latter.

I'm trying to find a cell with a string of "SALES" and then copy it 2
columns over.

The code as written copies the "SALES" cell (offset by 2 columns) to AT4.
Since that is not what you appear to expect, it may be that the copy
operation is taking place but you are looking in the wrong place.

If AT4 is in fact the correct copy destination, then I would simply make the
Sheets(1) === Sheets("Sheet1") amendment and verify that the copy operation
is successful.

If the intended destination is, however, the found cell offset by 2 columns,
are you sure that you want to copy the contents of the found cell, i.e that
both cells should read "SALES"? If this really is the case then you could
simplify the code with:

cell(1, 2).Value = cell.Value


---
Regards,
Norman



"scott" wrote in message
...
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

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