filling cells with formulas
 

Hi experts,
I have the folowing code which worked fine in the past but now, nothing
happens when changing a cell in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim sForm As String
Set myRng = Intersect(Target, Range("D:D"))
If myRng Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each myCell In myRng.Cells
sForm = "=if(OR(O4=""offerte"",O4=""afgesloten""),if(S4<" """,S4*U4,0),
0)"
Me.Cells(myCell.Row, "V").Formula = Replace(sForm, 4, myCell.Row)
sForm = "=if(I4<"""",Vlookup(I4,hulpblad!B2:C250,2),"""") "
Cells(Target.Row, "J").Formula = Replace(sForm, 4, Target.Row)
Next myCell
Application.EnableEvents = True
End Sub

any ideas?
thanks,
Pierre

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

filling cells with formulas
 

Hi Pierre,

Perhaps events have been disabled. Try:

Alt-F11 to open the VBE
Ctrl-G to access the Immediate window
Type Application.EnableEvents = True
Hit Enter

BTW, if your code changes Application settings, it is good practice to
restore the sttings in am error handler, e.g,:

'=========
Sub AAA()

On Error GoTo XIT
Application.EnableEvents = False

'Your code

XIT:
Application.EnableEvents = True
End Sub
'<<=========


---
Regards,
Norman


"Pierre via OfficeKB.com" <u13950@uwe wrote in message
news:599a70cb7d854@uwe...
Hi experts,
I have the folowing code which worked fine in the past but now, nothing
happens when changing a cell in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim sForm As String
Set myRng = Intersect(Target, Range("D:D"))
If myRng Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each myCell In myRng.Cells
sForm =
"=if(OR(O4=""offerte"",O4=""afgesloten""),if(S4<" """,S4*U4,0),
0)"
Me.Cells(myCell.Row, "V").Formula = Replace(sForm, 4, myCell.Row)
sForm = "=if(I4<"""",Vlookup(I4,hulpblad!B2:C250,2),"""") "
Cells(Target.Row, "J").Formula = Replace(sForm, 4, Target.Row)
Next myCell
Application.EnableEvents = True
End Sub

any ideas?
thanks,
Pierre

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

filling cells with formulas
 

Thanks Norman,

You were right !
Just one more question:

Because the cells in which the formulas go are not protect i get a red
triangle with a message with each cell.
Can i turn that off?
Thanks,
Pierre

Norman Jones wrote:
Hi Pierre,

Perhaps events have been disabled. Try:

Alt-F11 to open the VBE
Ctrl-G to access the Immediate window
Type Application.EnableEvents = True
Hit Enter

BTW, if your code changes Application settings, it is good practice to
restore the sttings in am error handler, e.g,:

'=========
Sub AAA()

On Error GoTo XIT
Application.EnableEvents = False

'Your code

XIT:
Application.EnableEvents = True
End Sub
'<<=========

---
Regards,
Norman

Hi experts,
I have the folowing code which worked fine in the past but now, nothing
[quoted text clipped - 23 lines]
thanks,
Pierre

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