Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling Formulas | Excel Discussion (Misc queries) | |||
filling formulas | Excel Discussion (Misc queries) | |||
Filling formulas | Excel Discussion (Misc queries) | |||
Formulas not filling in without F2+enter | Excel Programming | |||
Filling Formulas Across Workbooks | Excel Discussion (Misc queries) |