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

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

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

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

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