Function help:Public Function

[cooldyood]

I wrote the following function to copy data across sheets but it doesn't seem to be working. Please tell me if something obvious is wrong. TargetCell is supposed to be in Sheet4 and FoundCell in Sheet2.

Thx


Public Function FillCallsData(IYPName As String, TargetCell As Range) As Boolean
With Worksheets("Sheet2")
Set FoundCell = Cells.Find(What:=IYPName, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No call data for " & IYPName
TargetCell.Value = 0
Else
MsgBox "Address found is " & FoundCell.Address
Sheets("Sheet2").Activate
MsgBox FoundCell.Value
TargetCell.Value = FoundCell.Offset(0, 1).Value
End If
End Function

[stevebriz]

try this:

Public Function FillCallsData(IYPName As String, TargetCell As Range) As Boolean
With Worksheets("Sheet2")
Set FoundCell = Cells.Find(What:=IYPName, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
End With

If FoundCell Is Nothing Then
MsgBox "No call data for " & IYPName
TargetCell.Value = 0
Else
MsgBox "Address found is " & FoundCell.Address
Sheets("Sheet2").Activate
MsgBox FoundCell.Value

 Worksheets("Sheet4").Range(TargetCell.Address).Value = Worksheets("Sheet2").Range(FoundCell.Address).Offset(0, 1).Value
End If
End Function

Sub test()
FillCallsData "TESTSTRING", Range("A1")
End Sub

[T-J]

Set FoundCell = Cells.Find(What:=IYPName, After:=Range("A1"),

should be Set FoundCell = .Cells.Find(.... and the rest...)

[stevebriz]

You are right TJ..thanks for picking that up.