Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate a formula dynamically ?
Hi,
I need to generate a formula dynamically with VBA code. The following code generates the desired formula in the debug window. If I copy this into the cell the result is as expected. But I need the formula being generated dynamically by the Worksheet_Change() event. If the event is fired I get the error message: Error 1004 Application - or objectdefined error (translated from german) In the formula I call 2 user defined functions "Date2Julian" and "Str2Date" which expect cell values as parameters. Where is my mistake? Thank you very much for any hint. Best regards. Juergen Heyn, Wilhelmshaven, Germany 'Code: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change() ..." Dim rValid As Range Dim rCell As Range Dim sCellDate As String Dim sCellTime As String Dim sFormula As String Dim rRange As Range Application.ScreenUpdating = False If Target.Row = 8 And Target.Row <= 99 Then Select Case True Case Target.Column = 4 Set rValid = Range("D8:D99") For Each rCell In rValid If rCell = Target Then sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) sFormula = "=Wenn(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" Debug.Print sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormula Exit Sub End If Next rCell End Select End If Application.ScreenUpdating = False End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate a formula dynamically ?
Jurgen,
Get the formula working in a cell, and compare the formula to the string that you generate, to make sure that you have the correct syntax. The other problem is that you are changing a cell with the change event, which requires Application.EnableEvents = False when you start, and Application.EnableEvents = True when you finish. So, instead of using Exit Sub use GoTo FinishUp: and at the bottom of you code, us FinishUp: Application.ScreenUpdating = False Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi, I need to generate a formula dynamically with VBA code. The following code generates the desired formula in the debug window. If I copy this into the cell the result is as expected. But I need the formula being generated dynamically by the Worksheet_Change() event. If the event is fired I get the error message: Error 1004 Application - or objectdefined error (translated from german) In the formula I call 2 user defined functions "Date2Julian" and "Str2Date" which expect cell values as parameters. Where is my mistake? Thank you very much for any hint. Best regards. Juergen Heyn, Wilhelmshaven, Germany 'Code: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change() ..." Dim rValid As Range Dim rCell As Range Dim sCellDate As String Dim sCellTime As String Dim sFormula As String Dim rRange As Range Application.ScreenUpdating = False If Target.Row = 8 And Target.Row <= 99 Then Select Case True Case Target.Column = 4 Set rValid = Range("D8:D99") For Each rCell In rValid If rCell = Target Then sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) sFormula = "=Wenn(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" Debug.Print sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormula Exit Sub End If Next rCell End Select End If Application.ScreenUpdating = False End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate a formula dynamically ?
Hi Bernie,
thank you very much for your response. Unfortunately nothing changed the error is still fired: sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) 'sFormula = "=WENN(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" sFormulaE = "=IF(" + sCellTime + "<" + Chr(34) + Chr(34) + ",Date2Julian(" + sCellDate + ",Str2Hrs(" + sCellTime + ")),0)" Debug.Print sFormulaE Application.EnableEvents = False 'ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).FormulaLocal = sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormulaE Application.EnableEvents = True Best regards Juergen Heyn, Wilhelmshaven, Germany "Bernie Deitrick" <deitbe @ consumer dot org schrieb im Newsbeitrag ... Jurgen, Get the formula working in a cell, and compare the formula to the string that you generate, to make sure that you have the correct syntax. The other problem is that you are changing a cell with the change event, which requires Application.EnableEvents = False when you start, and Application.EnableEvents = True when you finish. So, instead of using Exit Sub use GoTo FinishUp: and at the bottom of you code, us FinishUp: Application.ScreenUpdating = False Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi, I need to generate a formula dynamically with VBA code. The following code generates the desired formula in the debug window. If I copy this into the cell the result is as expected. But I need the formula being generated dynamically by the Worksheet_Change() event. If the event is fired I get the error message: Error 1004 Application - or objectdefined error (translated from german) In the formula I call 2 user defined functions "Date2Julian" and "Str2Date" which expect cell values as parameters. Where is my mistake? Thank you very much for any hint. Best regards. Juergen Heyn, Wilhelmshaven, Germany 'Code: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change() ..." Dim rValid As Range Dim rCell As Range Dim sCellDate As String Dim sCellTime As String Dim sFormula As String Dim rRange As Range Application.ScreenUpdating = False If Target.Row = 8 And Target.Row <= 99 Then Select Case True Case Target.Column = 4 Set rValid = Range("D8:D99") For Each rCell In rValid If rCell = Target Then sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) sFormula = "=Wenn(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" Debug.Print sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormula Exit Sub End If Next rCell End Select End If Application.ScreenUpdating = False End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate a formula dynamically ?
Aaah. I see what your problem is. Range takes a string, not a range object:
I would use ActiveSheet.Cells(target.Row, target.Column + 1).Formula = sFormulaE HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi Bernie, thank you very much for your response. Unfortunately nothing changed the error is still fired: sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) 'sFormula = "=WENN(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" sFormulaE = "=IF(" + sCellTime + "<" + Chr(34) + Chr(34) + ",Date2Julian(" + sCellDate + ",Str2Hrs(" + sCellTime + ")),0)" Debug.Print sFormulaE Application.EnableEvents = False 'ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).FormulaLocal = sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormulaE Application.EnableEvents = True Best regards Juergen Heyn, Wilhelmshaven, Germany "Bernie Deitrick" <deitbe @ consumer dot org schrieb im Newsbeitrag ... Jurgen, Get the formula working in a cell, and compare the formula to the string that you generate, to make sure that you have the correct syntax. The other problem is that you are changing a cell with the change event, which requires Application.EnableEvents = False when you start, and Application.EnableEvents = True when you finish. So, instead of using Exit Sub use GoTo FinishUp: and at the bottom of you code, us FinishUp: Application.ScreenUpdating = False Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi, I need to generate a formula dynamically with VBA code. The following code generates the desired formula in the debug window. If I copy this into the cell the result is as expected. But I need the formula being generated dynamically by the Worksheet_Change() event. If the event is fired I get the error message: Error 1004 Application - or objectdefined error (translated from german) In the formula I call 2 user defined functions "Date2Julian" and "Str2Date" which expect cell values as parameters. Where is my mistake? Thank you very much for any hint. Best regards. Juergen Heyn, Wilhelmshaven, Germany 'Code: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change() ..." Dim rValid As Range Dim rCell As Range Dim sCellDate As String Dim sCellTime As String Dim sFormula As String Dim rRange As Range Application.ScreenUpdating = False If Target.Row = 8 And Target.Row <= 99 Then Select Case True Case Target.Column = 4 Set rValid = Range("D8:D99") For Each rCell In rValid If rCell = Target Then sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) sFormula = "=Wenn(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" Debug.Print sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormula Exit Sub End If Next rCell End Select End If Application.ScreenUpdating = False End Sub |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Generate a formula dynamically ?
Hi Bernie,
thank you very much for pointing me to the problem. Now everything works as it is supposed to do. Again thank you and have a nice evening. Best regards Juergen Heyn, Wilhelmshaven, Germany "Bernie Deitrick" <deitbe @ consumer dot org schrieb im Newsbeitrag ... Aaah. I see what your problem is. Range takes a string, not a range object: I would use ActiveSheet.Cells(target.Row, target.Column + 1).Formula = sFormulaE HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi Bernie, thank you very much for your response. Unfortunately nothing changed the error is still fired: sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) 'sFormula = "=WENN(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" sFormulaE = "=IF(" + sCellTime + "<" + Chr(34) + Chr(34) + ",Date2Julian(" + sCellDate + ",Str2Hrs(" + sCellTime + ")),0)" Debug.Print sFormulaE Application.EnableEvents = False 'ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).FormulaLocal = sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormulaE Application.EnableEvents = True Best regards Juergen Heyn, Wilhelmshaven, Germany "Bernie Deitrick" <deitbe @ consumer dot org schrieb im Newsbeitrag ... Jurgen, Get the formula working in a cell, and compare the formula to the string that you generate, to make sure that you have the correct syntax. The other problem is that you are changing a cell with the change event, which requires Application.EnableEvents = False when you start, and Application.EnableEvents = True when you finish. So, instead of using Exit Sub use GoTo FinishUp: and at the bottom of you code, us FinishUp: Application.ScreenUpdating = False Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP "Jürgen Heyn" wrote in message ... Hi, I need to generate a formula dynamically with VBA code. The following code generates the desired formula in the debug window. If I copy this into the cell the result is as expected. But I need the formula being generated dynamically by the Worksheet_Change() event. If the event is fired I get the error message: Error 1004 Application - or objectdefined error (translated from german) In the formula I call 2 user defined functions "Date2Julian" and "Str2Date" which expect cell values as parameters. Where is my mistake? Thank you very much for any hint. Best regards. Juergen Heyn, Wilhelmshaven, Germany 'Code: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change() ..." Dim rValid As Range Dim rCell As Range Dim sCellDate As String Dim sCellTime As String Dim sFormula As String Dim rRange As Range Application.ScreenUpdating = False If Target.Row = 8 And Target.Row <= 99 Then Select Case True Case Target.Column = 4 Set rValid = Range("D8:D99") For Each rCell In rValid If rCell = Target Then sCellDate = "B" + CStr(Target.Row) sCellTime = "D" + CStr(Target.Row) sFormula = "=Wenn(" + sCellTime + "<" + Chr(34) + Chr(34) + ";Date2Julian(" + sCellDate + ";Str2Hrs(" + sCellTime + "));0)" Debug.Print sFormula ActiveSheet.Range(Cells(Target.Row, Target.Column + 1)).Formula = sFormula Exit Sub End If Next rCell End Select End If Application.ScreenUpdating = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing a formula dynamically. | Excel Worksheet Functions | |||
How to make a formula cell dynamically display | Excel Worksheet Functions | |||
Dynamically Change date in Formula | Excel Worksheet Functions | |||
Using offset within a formula dynamically | Excel Worksheet Functions | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions |