lange Formel per VBA in Zelle schreiben

[Steffen Middelhuß]

Hallo Newsgroup,

ich habe ein kleines Problem ich möchte eine lange Formel in eine Zelle
schreiben, und zwar diese:

=WENN(J233<50;J233*0,05;WENN(ODER(J233>50,01;J233<500);2,5+(J233-50)*0,04;WE
NN(J233>500,01;20,5+(J233-500)*0,02)))/1,16

Diese soll auch so in der Zelle stehen, zunächst habe ich die aktuelle Zeile
in die Variable aktZei geschrieben und es dann so probiert:

Range("P" & aktZei) = "=WENN(J" & aktZei & "<50;J" & aktZei ...

bei kürzeren Formeln funktioniert das tadellos, bei dieser leider nicht,
habe auch darauf geachtet "Komma" als "Punkt" zu schreiben.

Hat von Euch jemand eine Idee?

mfg
Steffen

[stefan onken]

hallo Steffen,
probiers mal mit
Range("P" & aktZei).FormulaLocal = "=WENN(J" & aktZei usw

Es sollte auch mit Range("P" & aktZei).Formula gehen,
dann muss die Formel aber in englisch geschrieben werden,
also
Range("P" & aktZei).Formula = "=IF(J" & aktZei usw

Gruß
stefan

>-----Originalnachricht-----

>.
>

[Melanie Breden]

Hallo Steffen,

Steffen Middelhuß schrieb:

dann lass doch VBA die nötigen Ersetzungen vornehmen:

Sub WriteFormula()
Dim strFormel As String
Dim aktZeile As Integer

aktZeile = ActiveCell.Row
Const strBezug As String = "J233"
' Formel in einer Zeile
strFormel =

"=WENN(J233<50;J233*0,05;WENN(ODER(J233>50,01;J233<500);2,5+(J233-50)*0,04;WENN(J233>500,01;20,5+(J233-500)*0,02)))/1,16"

strFormel = Replace(strFormel, strBezug, "J" & aktZeile)
strFormel = Replace(strFormel, strBezug, ";" & ",")
strFormel = Replace(strFormel, strBezug, "," & ".")

Range("P" & aktZeile).FormulaLocal = strFormel
End Sub

Die Replace-Funktion steht ab Excel2000 zur Verfügung.
In vorherigen Versionen kann stattdessen die Substitute-Funktion
verwendet werden.

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)
#Excel-Auftragsprogrammierung#

[stefan onken]

ach, vergessen habe ich, das bei FormulaLocal Semikola in
der Formel stehen und bei Formula Kommata.

>-----Originalnachricht-----

>.
>

[Melanie Breden]

Hallo Steffen,

>> bei kürzeren Formeln funktioniert das tadellos, bei dieser leider nicht,
>> habe auch darauf geachtet "Komma" als "Punkt" zu schreiben.
>
> dann lass doch VBA die nötigen Ersetzungen vornehmen:
>
> Sub WriteFormula()
> Dim strFormel As String
> Dim aktZeile As Integer
>
> aktZeile = ActiveCell.Row
> Const strBezug As String = "J233"
> ' Formel in einer Zeile
> strFormel =
> "=WENN(J233<50;J233*0,05;WENN(ODER(J233>50,01;J233<500);2,5+(J233-50)*0,04;WENN(J233>500,01;20,5+(J233-500)*0,02)))/1,16"
> strFormel = Replace(strFormel, strBezug, "J" & aktZeile)
> strFormel = Replace(strFormel, strBezug, ";" & ",")
> strFormel = Replace(strFormel, strBezug, "," & ".")
>
> Range("P" & aktZeile).FormulaLocal = strFormel
> End Sub

upps... aus meinen Tests sind noch zwei Zeilen übrig geblieben.

Da die FormulaLocal-Eigenschaft verwendet wird müssen
die ';' und ',' ja nicht getauscht werden.
Lösche deswegen die beiden letzten Replace-Zeilen.
Oder etwas kürzer:

Sub WriteFormula()

Const strBezug As String = "J233"
' Formel in einer Zeile

strFormel = _

"=WENN(J233<50;J233*0,05;WENN(ODER(J233>50,01;J233<500);2,5+(J233-50)*0,04;WENN(J233>500,01;20,5+(J233-500)*0,02)))/1,16"

Range("P" & ActiveCell.Row).FormulaLocal = Replace(strFormel, strBezug, "J" & ActiveCell.Row)
End Sub

[Steffen Middelhuß]

Danke Melanie für deine Hilfe, genau so hats geklappt. Danke auch an Stefan.

"Melanie Breden" <Melanie.Br...@mvps.org> schrieb im Newsbeitrag
news:c7au97$1rnaq$1...@ID-200118.news.uni-berlin.de...

[Melanie Breden]

Hallo Steffen,

Steffen Middelhuß schrieb:

> Danke Melanie für deine Hilfe, genau so hats geklappt. Danke auch an Stefan.

freut mich dass es funzt und danke für die Rückmeldung.