problem getting formulas in cells that user adresses..

[Pierre via OfficeKB.com]

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

 

[Dave Peterson]

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"))

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??).