Please help me learn a little more VBA for custom function

Posted by Ken on December 05, 2001 3:30 PM

I am trying to accomplish this. If the cell selected by the custom function contains the word "Training" then the cell would be copied and pasted to the cell below. Here is what I have so far, I know I know, not very good, just trying to learn, Thank you.

Function CopyTraining(x)
If x = "Training" Then
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Else: Exit Function
End If
End Function

Posted by Colo on December 05, 2001 6:22 PM

Hi.Try This(but not Function)

Sub TestCopyTraining()
On Error GoTo ErrLine
Dim rngTrg As Range, strWrd As String, flg As Boolean
strWrd = "Training"
Set rngTrg = Application.InputBox("Please Select Target Range.", Type:=8)
With rngTrg
If InStr(1, .Value, strWrd) > 0 Then
If Not IsEmpty(.Offset(1)) Then
If MsgBox(.Offset(1).Address & ":Can I Replace?", 4) = 6 Then flg = True
Else
flg = True
End If
If flg = True Then .Copy .Offset(1)
End If
End With
ErrLine:
End Sub

Posted by Bariloche on December 05, 2001 7:39 PM

just to clarify

Ken,

Functions can't perform actions. They can only return a value.

I haven't looked at Colo's recommended code, but that's why Colo changed it from a function to sub procedure.

take care

Posted by Ken on December 06, 2001 11:59 AM

Thanks Bariloche and Colo, very nice Macro