Formel einfügen, wie "unten füllen"

[Jörg]

Hallo Fachleute,
mit nachstehendem Makro möchte ich die folgende Formel so einfügen, dass sie sich praktisch an die nächste Zeile anpaßt.

Formel

Code: Alles auswählen

=WENN(ISTLEER(I138);"";(I138+14))

Makro

Code: Alles auswählen

sub Formeln_uebertragen
 odoc = ThisComponent
 sheet = odoc.sheets(4)

Sheet = ThisComponent.CurrentController.ActiveSheet   
c = Sheet.getCellRangeByName("AI1:AT4000").queryEmptyCells
getFirstEmptyRowInColumn = Split(c.RowDescriptions(0)," ")
oRow = getFirstEmptyRowInColumn(1)
mycell =  Sheet.getCellRangeByName("AI" & oRow)   
myView = thisComponent.CurrentController
mycell.FormulaLocal = ???????

end sub

Beim nächsten Ausführen des Makros müßte die Formel dann so lauten.

Code: Alles auswählen

=WENN(ISTLEER(I139);"";(I139+14))

dann I140 usw.
Habe noch weitere Formeln in so einer Zeile. Deshalb der Bereich AI1:AT4000
z.Bsp.

Code: Alles auswählen

=WENN(AI138="";"";WENN(H138="Ja";"";WENN(HEUTE()>AI138+14;AI138+14;"")))

Vielen Dank für Eure Anregungen.

[F3K Total]

Moin,
ich glaube, das, was du erreichen willst, kannst du auch ohne Makro, mit Formeln, hinbekommen.
Dazu müsstest du es aber im Calc-Forum genau beschreiben, und eine Beispieldatei hochladen.
Gruß R

[Jörg]

Hallo R,
ich frage mal anders.
AI138: AT138 ist der letzte gefüllte Zellbereich
Wie bekomme ich Zugriff auf diesen Bereich.
Dann diesen Bereich kopieren und in AI139:AT139 einfügen usw.

Ich möchte damit das manuelle "Unten Füllen" umgehen. Kann man ja mal "vergessen".
Und vorsorglich unendliche viele Zeilen füllen ????

Jörg

[F3K Total]

Hi,

Wie bekomme ich Zugriff auf diesen Bereich.

Anbei ein Beispiel mit Matrixformel in B1 und Hyperlink in C1.
Klicke auf C1.
Es werden nur die Spalten, die berechnet werden, markiert.
Dann STRG+C, Pfeil nach unten, STRG+V
Eintragungen in Spalte A und B, fertig.
Gruß R

[balu]

Hallo Jörg,

mit nachstehendem Makro möchte ich die folgende Formel so einfügen, dass sie sich praktisch an die nächste Zeile anpaßt.

So wie Du höchstwahrscheinlich denkst geht das nicht!

Und nun die ausführliche Antwort.

Code: Alles auswählen

mycell.FormulaLocal

Das ist schon mal der richtige weg.
ABER!!!
Um eine Calc-Formel wie z.B.

Code: Alles auswählen

=WENN(ISTLEER(I138);"";(I138+14))

per Makro mit automatischer Adressierung einzutragen, also das anpassen der Zell und/oder Zeilenbezüge, musst Du die Formel zerpflücken. Auch wenn eine einfache Formel wie z.B.

Code: Alles auswählen

=A2*B2

per Makro eingetragen werden kann, so ist sie für das Makro selber nix anderes als reiner Text. Und woher soll das Makro im Text wissen wo sich was und wann ändern soll. Das geht also nicht ganz so einfach, denn die erst genannte "Text-Formel" muss so zerlegt werden, das das Makro etwas zum berechnen hat. Und das würde so rein theoretisch wie folgt aussehen.

Code: Alles auswählen

for i = 138 to 200
tstformel ="WENN(ISTLEER(I" & i & ");""" & """;I" & i &"+14)"
next i
mycell.FormulaLocal  = "=" & tstformel

Du siehst das aus einer relativ kurzen Calc-Formel schon eine kleine Monster-Text-Formel geworden ist. Und das alles nur, weill in diesem Fall der Zeilenzähler i mit einer For-Next-Schleife eingebaut wurde.

Hier fällt das noch gar nicht so sehr ins Gewicht, aber die typischen Zeichen für eine Leerzelle die "" müssen mit Vorsicht behandelt werden da man durch ihnen sehr schnell ins stolpern geraten kann. Das eben gezeigte war ja recht harmlos, aber überleg dir mal was Du da für einen Spaß bekommst wenn Du diese deine Formel auseinanderpflücken musst.

Code: Alles auswählen

=WENN(AI138="";"";WENN(H138="Ja";"";WENN(HEUTE()>AI138+14;AI138+14;"")))

Okay, man kann das wohl machen, aber je mehr Formeln man hat, und je umfangreicher sie sind, um so unangenehmer wird das ganze dann.

Kurz und Gut, auf diesem Wege wird man nicht glücklich, nein wirklich nicht.
Es müsste doch noch eine alternative geben.
Und in der Tat, die gibt es.
Als erstes musst du einen Namen für die Formel vergeben
-> Menü Einfügen
-> Namen
-> Festlegen ... Strg + F3
Und dort trägst Du deine Formel ein, als Beispiel die

Code: Alles auswählen

WENN(ISTLEER(I138);"";I138+14)

und vergibst ihr den Namen test.

Jetzt kannst Du per Makro dir die Formel automatisch eintragen lassen, welches mal als minimalbeispiel wie folgt aussieht.

Code: Alles auswählen

Sub Main
myDoc = thisComponent
mySheet = myDoc.sheets(0)

for i = 138 to 200

mycell = mysheet.getCellRangeByName("J" & i)
mycell.FormulaLocal  = "=test" 

next i

End Sub

Bedenke aber bitte folgendes dabei. In der Zelle steht nicht mehr die Formel, sondern der eben zugewiesene Name, also =test.

Hab dir mal ein kleines Beispiel angehängt. Wenn Du dazu fragen hast, meld dich einfach.



Gruß
balu

[Karolus]

Hallo

Code: Alles auswählen

fillrange= sheet.geCellRangeByName("AT139:AI222")
fillrange.fillAuto(0, 1) 

Karolus

[balu]

Hallo Jörg,

hatte vergessen gehabt zu sagen, dass der Name für die Formel test nur ein Beispiel ist. Du kannst auch z.B. Sonderrechnung nehmen, oder was Du willst. Jedoch die Funktionsnamen von Calc, wie z.B. WENN oder WOCHENTAG etc. sind absolut Verboten.



Gruß
balu

[Jörg]

Hallo,
danke für Eure Antworten.

@F3K
ist mir leider ein bißchen zu umständlich, da ich in der betreffenden Tabelle keine manuellen Eingaben vornehme.
@ balu

Code: Alles auswählen

for i = 138 to 200
tstformel ="WENN(ISTLEER(I" & i & ");""" & """;I" & i &"+14)"
next i
mycell.FormulaLocal  = "=" & tstformel

Ja genau daran bin ich gescheitert. Es gibt im Forum dazu eine Code Stichwort "Zeiterfassung".
Nun zu Deinem Vorschlag.
Den Formeln Namen zu geben habe ich verstanden. Hatten wir ja schon mal irgendwann. Danke für den Hinweis, den Du gerade eingestellt hast.

Code: Alles auswählen

for i = 138 to 200

definiert den Bereich J138:J200.
Das bedeutet, dass der kpl. Bereich mit der entsprechenden Formel gefüllt wird. D.h., dass ich entweder den Bereich von vornherein größer machen oder irgendwann das Makro anpassen muß.
Ich hänge mal Deine Datei nochmal dran. Der Schaltfläche hab ich mal mein Makro zugeordnet.
Ginge es nicht so, dass nachdem mein Makro durchgelaufen ist, die entsprechenden Formeln eingetragen werden und zwar nur in der Zeile, die gerade
vom Makro bearbeitet wurde oder meinetwegen den nächsten Zellbereich vorbelegt.

@ Karolus
Hab ich da nicht das gleiche Problem?

Gruß Jörg

Formeln_eintragen_1.ods

(17.8 KiB) 58-mal heruntergeladen

[Karolus]

Hallo

@ Karolus
Hab ich da nicht das gleiche Problem?

Wieso gleiches ? - Ich hab kein Problem - du hast das Ansinnen geäussert:

ich frage mal anders.
AI138: AT138 ist der letzte gefüllte Zellbereich
Wie bekomme ich Zugriff auf diesen Bereich.
Dann diesen Bereich kopieren und in AI139:AT139 einfügen usw.

[balu]

Hallo Jörg,

Den Formeln Namen zu geben habe ich verstanden. Hatten wir ja schon mal irgendwann.

Das ist natürlich gut das Du dich daran erinnern kannst, weil dir das dadurch nicht ganz so unbekannt ist.

definiert den Bereich J138:J200.

War nur ein Beispielbereich für das mehrfache Formeln eintragen.

Das bedeutet, dass der kpl. Bereich mit der entsprechenden Formel gefüllt wird. D.h., dass ich entweder den Bereich von vornherein größer machen oder irgendwann das Makro anpassen muß.

Ich dachte Du wärst von allein dahinter gekommen. War aber wohl ein Fehler meinerseits.

Also denne.
Du hast doch schon etwas eingebaut um die Letzte benutzte Zeile zu lokalisieren. Oder täusche ich mich da? Und durch diese Lokalisierung bekommst Du doch einen Wert geliefert, der die Zeilennummer zurück gibt. Und diesen Wert nimmst Du und setzt ihn dementsprechend für die Zellposition ein.

Hier mal ein Beispiel wie das aussehen könnte.

Code: Alles auswählen

	oStammSheet = ThisComponent.sheets.getByName("Tabelle2")
		With oStammSheet ' Letzte Zeile der Spalte AI im 2. Tabellenblatt ermitteln
			x = .Columns(34).queryEmptyCells()
			iLetzteZeile = x(x.Count-1).RangeAddress.StartRow +1
		End With

			mycell = oStammSheet.getCellRangeByName("AI" & iLetzteZeile )
			mycell.FormulaLocal  = "=test"

Verstehst Du es jetzt?



Gruß
balu

[Jörg]

Hallo balu,
ich danke Dir.
In der Mustertabelle funktioniert es.

Formeln_eintragen_1.ods

(20.44 KiB) 73-mal heruntergeladen

Formel

Code: Alles auswählen

WENN(ISTLEER($I17);"";($I17+14))

Werd das an meine Originaltabelle anpassen mit den anderen Formeln.

Code: Alles auswählen

sub RechnungErstellen
rem-----------------------------------------------------------------------
antwort = msgbox ("Wollen Sie die Rechnung erstellen?",36,"Rechnung erstellen")
  if antwort = 7 then
  exit sub
  end if
rem ----------------------------------------------------------------------
 odoc = ThisComponent
        osheet = odoc.sheets(0)
        orange = osheet.getCellRangeByName("A1:AH1")
        orangeAddress = orange.getrangeaddress
        odata()= orange.getdataarray
        oZielSheet = oDoc.sheets(1)
        c = oZielSheet.getCellRangeByName("A1:A12000").queryEmptyCells
        oZielCell = c(0).getcellbyposition(0,0)
        oZielCellAddress = oZielCell.getcelladdress
        X1=oZielCellAddress.column
        Y1=oZielCellAddress.row
        X2=oZielCellAddress.column+(orangeAddress.EndColumn-orangeAddress.StartColumn)
        Y2=oZielCellAddress.Row+(orangeAddress.EndRow-orangeAddress.StartRow)
        oZielrange=oZielSheet.getCellRangeByPosition(X1,Y1,X2,Y2)
        oZielrange.setdataarray(odata())
       
rem-----------------------------------------------------------------
fuellen

end sub

Sub fuellen

 odoc = ThisComponent
 sheet = odoc.sheets(1)
   oStammSheet = ThisComponent.sheets.getByName("Tabelle2")
      With oStammSheet ' Letzte Zeile der Spalten AI:AT im 2. Tabellenblatt ermitteln
         x = .Columns("34:45").queryEmptyCells()
         iLetzteZeile = x(x.Count-1).RangeAddress.StartRow +1
      End With

         mycell = oStammSheet.getCellRangeByName("AI" & iLetzteZeile )
         mycell.FormulaLocal  = "=test"
          mycell = oStammSheet.getCellRangeByName("AJ" & iLetzteZeile )
         mycell.FormulaLocal  = "=tester"
'usw.---------ich denk mir noch bessere Namen aus

End Sub

Nochmal danke balu