VBA absoltuer Zellebezug teilweise in kopierte Formel!

[cysu11]

Hallo zusammen,


übe gerade fleißig Excel und VBA versuche auch anderen zu helfen. In diesem Zusammenhang habe ich eine Frage wo ich nicht weiterkomme...

Im A1 steht die Formel: =B1+C1+D1+E1 diese Formel kopiere ich per VBA nach A10 z.B. und durch die relativen Bezüge passt diese sich an =B10+C10+D10+E10, soweit so gut!
Ich möchte aber nach dem Kopiervorgang, wie kann ich per VBA das draus machen? =$B$10+$C$10+$D$10+E1, das nächste mal kopiere ich wieder die Formel aus A1 nach A15 z.B. und dann soll auch wieder nach dem Kopieren, der relative Zellbezug der ersten 3 Zellen in Absolut umgewandelt werden das letzte bleibt relativ!?


Vielen Dank
LG
Alexandra

[MisterBurns]

Der Spaß geht so

Code:

Sub ersetzen()
Dim zeichen As Integer, laenge As Integer, i As Integer
Dim Formel As String

Range("A1").Copy
Range("A10").PasteSpecial xlFormulas

With Range("A10")
   .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1, xlAbsolute, Range("A10"))
   Formel = .Formula
End With

For i = 1 To 2
   laenge = Len(Formel)
   zeichen = InStrRev(Formel, "$")
   Formel = Left(Formel, zeichen - 1) & Right(Formel, laenge - zeichen)
Next i

Range("A10").Formula = Formel

End Sub

Das Ändern von relativ zu absolut ginge mit einem Einzeiler (bzw. Dreizeiler in diesem Beispiel):

Code:

With Range("A10")
   .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1, xlAbsolute, Range("A10"))
End With

und xlRelative für den umgekehrten Weg. Nur dein Sonderwunsch macht es etwas komplizierter.

[cysu11]

Hi Berni,



vielen lieben Dank! Ich hab gedacht ich kann das auf meine Formel anpassen, aber leider bekomme ich das nicht hin! :(

Seit eine Stunde versuche ich zu blicken, was du da gezaubert hast und ich verstehe nur Bahnhof! :)

Damit es einfach bleibt, habe ich die Formel =B1+C1+D1+E1 vorhin angegeben und dachte ich kann es mir dann schon zurecht basteln auf meine Formeln! :(
Ausgangsbasis, also zu kopierender Bereich, das als Vorlage dient:

=WENNFEHLER(H5-(((P4-Q4)*(H5*100)/I4)/100);0)
=WENNFEHLER(H6-(((P4-Q4)*(H6*100)/I4)/100);0)
=WENNFEHLER(H7-(((P4-Q4)*(H7*100)/I4)/100);0)
=WENNFEHLER(H8-(((P4-Q4)*(H8*100)/I4)/100);0)
=WENNFEHLER(H9-(((P4-Q4)*(H9*100)/I4)/100);0)
=WENNFEHLER(H10-(((P4-Q4)*(H10*100)/I4)/100);0)
=WENNFEHLER(H11-(((P4-Q4)*(H11*100)/I4)/100);0)
=WENNFEHLER(H12-(((P4-Q4)*(H12*100)/I4)/100);0)
=WENNFEHLER(H13-(((P4-Q4)*(H13*100)/I4)/100);0)
=WENNFEHLER(H14-(((P4-Q4)*(H14*100)/I4)/100);0)

und so soll es nach dem kopieren aussehen, wenn ich z.B. ab Zelle A25 einfüge!


=WENNFEHLER(H25-((($P$4-$Q$4)*(H25*100)/$I$4)/100);0)
=WENNFEHLER(H26-((($P$4-$Q$4)*(H26*100)/$I$4)/100);0)
=WENNFEHLER(H27-((($P$4-$Q$4)*(H27*100)/$I$4)/100);0)
=WENNFEHLER(H28-((($P$4-$Q$4)*(H28*100)/$I$4)/100);0)
=WENNFEHLER(H29-((($P$4-$Q$4)*(H29*100)/$I$4)/100);0)
=WENNFEHLER(H30-((($P$4-$Q$4)*(H30*100)/$I$4)/100);0)
=WENNFEHLER(H31-((($P$4-$Q$4)*(H31*100)/$I$4)/100);0)
=WENNFEHLER(H32-((($P$4-$Q$4)*(H32*100)/$I$4)/100);0)
=WENNFEHLER(H33-((($P$4-$Q$4)*(H33*100)/$I$4)/100);0)
=WENNFEHLER(H34-((($P$4-$Q$4)*(H34*100)/$I$4)/100);0)

 Vielen lieben Dank
Alexandra

[MisterBurns]

Liebe Alexandra,

das finde ich jetzt etwas ärgerlich, denn hättest du gleich von Anfang an die richtige Formel bekanntgegeben, wäre die Lösung ganz anders ausgefallen. Diese Formel muss nun gänzlich anders behandelt werden. Ich habe mich daran orientiert, dass die letzte Stelle der Formel nicht absolut werden darf. Nun ist es auf einmal so, dass zwei Stellen mittendrin in der Formel auf absolut umgestellt werden sollen.

Und bevor ich mir weiter Gedanken mache, beantworte bitte zuerst, ob der Teil "P4-Q4" bzw. "$P$4-$Q$4" variabel sein muss oder nicht. Denn wenn nicht, dann verwende doch einfach Ersetzen:

Code:

Range("A25").Formula = Replace(Range("A1").Formula, "P4-Q4", "$P$4-$Q$4")
Range("A25").Formula = Replace(Range("A25").Formula, "I4", "$I$4")

[cysu11]

Hallo Berni,


sorry,  wie gesagt ich wollte es einfach halten!

Ja, "P4-Q4" bzw. "$P$4-$Q$4" und $I$4 bzw. I4 müssen variabel sein, deswegen kopiere ich diese Formel ohne absoluter Bezug diese passt sich dann entsprechend an, und danach möchte ich die absoluten Bezüge setzen, damit beim Zeilen einfügen etc. damit diese immer ihre absoluten werte behalten!

Vielen Dank & sorry nochmals, das nächste mal stellt ich gleich die richtige Formel rein! :(

LG
Alexandra

[Flotter Feger]

Hallo Alexandra,

mal ein Tip ... schreib doch die Formel gleich absolut in die erste Zelle, dann brauchst du das Umbauen nicht.
Die Originalformel interessiert es sicher nicht, ob sie absolut oder relativ ist ... oder könnte sie deshalb von anderen Formeln gemobt werden ?  :17:

[cysu11]

Hi Sabina,


wenn die Formel gleich absolute Bezüge hat dann passt sie sich nicht mehr an an der neuen Umgebung sonder behält immer starr die Bezüge bei.
Ich möchte das die Formel zuerst sich anpassen an der neuen Umgebung und diese dann festmachen für den oft vorkommenden Fall, dass man Zeilen einfügt mitten drin im kopierte Bereich. Denn dann würde die Formeln die unter der eingefügten Zeilen ohne abholuten Bezug, Ihren Bezug verlieren!

LG
Alexandra

[MisterBurns]

Wenn es heute P4:Q4 und morgen aber M5:N5 ist, dann sehe ich keinen simplen Weg, das zu bewerkstelligen. Die Möglichkeit, die komplette Formel auf relativ/absolut zu setzen, habe ich dir bereits gezeigt. Aber wie willst du VBA sagen, dass der Bezug einmal geändert werden soll und einmal wieder nicht. Die Stelle der betreffenden Zeichen ändern sich ja auch in Abhängigkeit der Zeilennummer.
Die einzige Idee, die ich noch hätte, wäre der Weg über eine Inputbox.

[cysu11]

Hi Berni,


spalte bleiben immer gleich nur die Zeilen ändern sich!

"P4-Q4" bzw. "$P$4-$Q$4" und $I$4 bzw. I4 
"P25-Q25" bzw. "$P$25-$Q$25" und $I$25 bzw. I25 

LG
Alexandra

[MisterBurns]

Na dann verweise ich nochmal auf meine Lösung von vorhin:

Code:

Range("A25").Formula = Replace(Range("A1").Formula, "P4-Q4", "$P$4-$Q$4")
Range("A25").Formula = Replace(Range("A25").Formula, "I4", "$I$4")

Mach daraus

Code:

Range("A25").Formula = Replace(Range("A1").Formula, "(P", "($P$")
'usw.usw.

Kommst du damit klar?