Generate a formula dynamically ?

  • Thread starter Thread starter Jürgen Heyn
  • Start date Start date
J

Jürgen Heyn

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