problem getting formulas in cells that user adresses..

Hi experts,

I got the following code from Tom Ogilvy (very good because it works as a
charm)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Column = 4 Then
sForm = "=if(OR(P4=""offerte"",P4=""afgesloten""),if(T4<>"""",T4*V4,0),0)"
Cells(Target.Row, "W").Formula = Replace(sForm, 4, Target.Row)
end sub

This works if the user changes a cell in column 4 but it does not work if the
user copies a range of cells in lets say B10..B20

Is there a way to adapt the code above so that no matter what the user does,
if something is put in column 4 the formula has to be put in column W. ?

Thanks,
Pierre

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Your code checks to see how many cells are in the target and quits if it's more
than one.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim sForm As String

Set myRng = Intersect(Target, Me.Range("d:D"))

If myRng Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False
For Each myCell In myRng.Cells
sForm _
= "=if(OR(P4=""offerte"",P4=""afgesloten""),if(T4<>"""",T4*V4,0),0)"
Me.Cells(myCell.Row, "W").Formula = Replace(sForm, 4, myCell.Row)
Next myCell
Application.EnableEvents = True

End Sub

I think I may have stayed away from using 4 in the formula--maybe some other
character would be less confusing (# maybe??).



"Pierre via OfficeKB.com" wrote:
>
> Hi experts,
>
> I got the following code from Tom Ogilvy (very good because it works as a
> charm)
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count = 1 Then
> If Target.Column = 4 Then
> sForm = "=if(OR(P4=""offerte"",P4=""afgesloten""),if(T4<>"""",T4*V4,0),0)"
> Cells(Target.Row, "W").Formula = Replace(sForm, 4, Target.Row)
> end sub
>
> This works if the user changes a cell in column 4 but it does not work if the
> user copies a range of cells in lets say B10..B20
>
> Is there a way to adapt the code above so that no matter what the user does,
> if something is put in column 4 the formula has to be put in column W. ?
>
> Thanks,
> Pierre
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200512/1

--

Dave Peterson