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