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

problem getting formulas in cells that user adresses..
 

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