VBA - nach Formel Funktion den Inhalt fixieren

[RoboMarvin]

Hallo zusammen,

Zu mir:
ich bin seit einigen Jahren Excel-Anwender u komme soweit mit Formeln gut zurecht.

Jetzt bin ich allerdings an einem Punkt angelangt, an dem ich (meines Verständnisses nach) nur noch mit VBA weiterkomme - und DAS kann ich kein Stück.
Ich habe schonmal ein bisschen im Editor rumgespielt, um einfache, aus dem Netz zu findende Kleinprogramme auszuprobieren.
Da das meiste copy->paste war, sag ich mal locker "so weit so gut"

Zum Problem:
In der angehängten Datei seht ihr in Spalte A (Tabelle 'Baukosten') die Formel =WENN(F11="";"";1+MAX(A$10:A10))
...diese erlaubt es mir bei Firmen-Eingabe (F) eine Fortlaufende Zahl auszuspucken, welche mir über S-Verweis wiederum erlaubt das Tabellenblatt 'Zahlungsfreigabe' zu füttern.
Klappt auch fast prima, nur, wenn ich
a) einen Eintrag ('Baukosten', "F") lösche, werden die Zahlen auf die Anzahl (ähnlich der "RANG"-Funktion) angepasst.
damit könnt ich auch noch leben - dann wird halt einfach nix gelöscht.
allerding
b) ändert sich auch die Reihenfolge - Beispiel

in Zeile 13 steht Firma Moin (Spalte A = 1)
Zeile 14 steht Firma Tach (Spalte A = 2)
und nun in Zeile 12 Fa. Hello (Spalte A12 wird zur 1, A13 zur 2, A14 zur 3)

Dementsprechent sind alle Werte in der Tabelle 'Nachträge an der falschen Stelle.

Ich würde nun gerne ein Makro haben wollen, daß nach dem Eintrag in Spalte "F" den Inhalt der Formel in "A" übernimmt und alle anderen Formeln in "A" ganz normal weiterzählen.

Habs hiermit ausprobiert - bin scheinbar aber zu dämlich es zu aktivieren:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim zeile As Long

If Target.Column = 6 Then
zeile = Target.Row
If Target.Value > 0 Then
Tabelle1.Cells(zeile, 1) = Target.Value
End If

End If
End Sub

Hat jemand eine Idee

Viiiilen liben Dank im Voraus!!!!

Flo

 

[blackraven]

Ich würde nun gerne ein Makro haben wollen, daß nach dem Eintrag in Spalte "F" den Inhalt der Formel in "A" übernimmt und alle anderen Formeln in "A" ganz normal weiterzählen.

Du möchtest das ein Makro die also deine NR. als Text in Spalte A einfügt und alle die "leer" sind weiter berechnen, sofern man bei Firma etwas einträgt?

Deine Formel in A mit "MAX" zählt dann aber auch nur bei der höchsten Zahl weiter.
Wenn du 1,2,3,4 vergeben hast und 3 löscht, dann macht er bei 5 weiter und füllt die 3 nicht mehr auf.

Auch nimmt die Formel ja nur das Max aus den darüber stehenden an.
Wenn du also bei den Abbruchmaßnahmen anfängst und dort die 1,2,3,4,5 vergeben werden, wissen das die Formeln darüber nicht mehr, du bekommst dann also doppelte Zahlen.
Vorher haben sich die unteren Zahlen ja durch die Eingabe oben aktualisiert, das passiert beim Text nicht mehr.

 

[Cordesh]

Hab schon sehr lange nix mehr mit VBA gemacht und kann Dir daher nicht direkt helfen, aber ich habe damals bei

https://www.herber.de/

extrem viel Hilfe gefunden. Excel Herber ist schon seit 20 Jahren die #1 auf dem Gebiet.

 

[Scientist]

Als erstes zwei Anmerkungen:
Code bitte immer in Code-Tags packen.
Ein Minimalbeispiel mit Inhalt waere hilfreicher.

Nun denn ...
Nach Eingabe in Spalte F soll eine fortlaufende Zahl eingetragen werden?
Muss sie fortlaufend sein? Man koennte auch die Zeilennummer verwenden, wenn die Positionen fix bleiben.

Ansonsten zum Makro:
An und fuer sich funktioniert es, nur wird aktuell die Eingabe in Spalte F in Spalte A eingefuegt (Target.Value -> Inhalt der bearbeiteten Zelle).
Wo hast du denn das Makro eingefuegt? Ist es denn auch bei Tabelle1 hinterlegt oder woanders?
Sicher dass es nicht ausgefuehrt wird oder nur nichts sichtbar passiert?

 

[RoboMarvin]

Moin zusammen,

@blackraven: ich könnte mich damit anfreunden nix zu löschen, nachdem eine Firma eingetragen wurde... wenn dann - so wie du es glaube ich beschreibst - ganz normal weitergezählt wird.
Das Makro müsste also "nur" die Formel - durch den von der Formel selbst errechneten Wert - überschreiben, damit diese weiterzählen kann. Ooooder eben die errechneten Werte fixieren.
Alternativ ein Makro, welches die Formel in "A" überflüssig macht und gerade beschriebenes mit einem Abwasch macht.
Ob die Zahl nun als Zahl oder als Text formatiert wird/ist, müsste dem SVerweis ja eiiiiigentlich Wurscht sein, oder?

@Cordesh: danke für deinen Tipp!! Sollte ich hier nicht weiterkommen, werde ich das probieren.
Vorerst möchte ich es aber vermeiden "2 Baustellen" zu eröffnen, in welche liebe Helfer wie ihr Hirnschmalz reinstecken

Danke nochmals an euch!!
Flo

Ergänzung (24. Februar 2020)

Code bitte immer in Code-Tags packen.
Ein Minimalbeispiel mit Inhalt waere hilfreicher.


Tut mir leid, das wusste ich nicht... ...und jetzt wo ich's weiß, wüsste ich nicht einmal, wie man "Code-Tags" erzeugt


Nun denn ...
Nach Eingabe in Spalte F soll eine fortlaufende Zahl eingetragen werden?
Muss sie fortlaufend sein? Man koennte auch die Zeilennummer verwenden, wenn die Positionen fix bleiben.

Es könnte theoretisch auch die Zeilennummer sein... wie würde ich es dann aber am gescheitesten lösen, dass die 2. Tabelle fortlaufend gefüllt wird - und der erste SVerweis Eintrag nicht in Zeile 555 (als Beispiel) landet?


Ansonsten zum Makro:
An und fuer sich funktioniert es, nur wird aktuell die Eingabe in Spalte F in Spalte A eingefuegt (Target.Value -> Inhalt der bearbeiteten Zelle).

Aaaahhhh... ok - werd ich morgen mal ausprobieren

Wo hast du denn das Makro eingefuegt? Ist es denn auch bei Tabelle1 hinterlegt oder woanders?
Sicher dass es nicht ausgefuehrt wird oder nur nichts sichtbar passiert?


DAS ist genau das Ding; ich füge ein Modul im ersten Tabellenblatt ein, füge den Code ein und wenn ich F5 drücke, öffnet sich ein neues Modul-Fenster sozusagen. Also, "ausführen" ist ausgegraut und ich darf nur ein neues Modul erstellen.

Ich würde gerne präzieser sein - jedoch ist es tatsächlich meine ALLERERSTE VBA Handlung die ich hier versuche auf die Beine zu stellen


Danke, dass du dir die Zeit genommen hast!!
Flo

Ergänzung (24. Februar 2020)

Schiete - selbst das "Zitieren" hab ich nicht drauf... der letzte post sollte an Scientest gehen

 

[Scientist]

Fuer Tags, siehe Anhang.

Der Code gehoert auf die Seite "Tabelle1 (Baukosten)" im VBA-Editor und nicht in ein Modul (siehe Anhang).
Ansonsten wird das Change-Event nicht getrackt.

Zur Idee mit den Zeilen:
War keine gute Idee, weil beim Loeschen sich dann Tabelle3 (Nachtraege) veraendert.

Zum Makro:
Das Ziel ist doch eingentlich Spalte D und F aus Tabelle1 in Tabelle3 einzufuegen.
Dann mach das doch direkt, statt erst eine Nummer einzutragen und dann mit SVERWEIS() zu arbeiten ...

 

[RoboMarvin]

Hallo Scientist,

vorab: wiedermal danke, daß du dir die Mühe machst mir zu helfen.

Ich weiß nicht, ob ich dich richtig verstehe, was "die Idee mit den Zeilen" betrifft;
ich muss nix löschen. Nur mal angenommen, ich hätte einen falschen Eintrag in Tabelle1 gemacht, würde ich die betroffenen Zeilen einfach ausblenden und die Spalten mit den Geldern auf 0 setzen, damit nix in die Gesamtberechnungen einfließt.

Ich weiß leider nicht, was du mit "
Das Ziel ist doch eingentlich Spalte D und F aus Tabelle1 in Tabelle3 einzufuegen.
Dann mach das doch direkt, statt erst eine Nummer einzutragen und dann mit SVERWEIS() zu arbeiten ...

Also, ja - das ist das Ziel... ...meinst du mit "direkt", ich soll das Manuell in die Liste einpflegen? Falls ja, ist es ja genau das, was ich vermeiden möchte.
Falls nein: bitte um Erläuterung


Schönen Tag zusammen
Flo

Ergänzung (25. Februar 2020)

sagt mal: wie wär es denn mit einem Makro, welches die Formel (Tabelle1, Spalte A) überflüssig macht, indem man einen Code schreibt, der das tut...
Also:
Wenn Eintrag in Spalte F stattfindet, dann zähle fortlaufend (beginnend mit 1).
Und zwar fortlaufende Zahl, unabhängig von der Zeilen-Position.

Damit wäre alles geritzt (aus meiner Anfänger-Sicht)

Hat jemand eine Idee?

Flo

Ergänzung (25. Februar 2020)

Ich habe da was gefunden.. aber auch hier ist das Problem, daß die Reihenfolge der Einträge berücksichtigt wird..

Public Sub Nummerierung()
    Dim avntInput As Variant, avntOutput() As Variant
    Dim lngCount As Long, ialngIndex As Long
    avntInput = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp)).Value
    ReDim avntOutput(LBound(avntInput) To UBound(avntInput), 0)
    For ialngIndex = LBound(avntInput) To UBound(avntInput)
        If Not IsEmpty(avntInput(ialngIndex, 1)) Then
            lngCount = lngCount + 1
            avntOutput(ialngIndex, 0) = lngCount
        End If
    Next
    Cells(2, 1).Resize(UBound(avntOutput), 1).Value = avntOutput
End Sub

Und was in dem Code ist eigentlich falsch, wenn ich beim drücken von F5 "dieses" Fenster erhalte??
Dieses Mal war ich NICHT im Modul, sondern auf Tabelle 1

 

[blackraven]

Ich würde nicht irgendwo Code "klauen" wenn ich die Grundlagen nicht kenne.

Das kam mir auch in den Sinn, Ein Button in Tabelle1 welcher das Makro ausführt und die Nummerierung in SpalteA übernimmt. Um dann bei jedem Durchlauf beim höchsten Wert weiter zu Nummerieren könnte man noch eine Max-Formel in A10 setzen, er soll die alten Werte dann ja auslassen und nur die neuen weiter Nummerieren oder?

Das wäre aus meiner Sicht recht einfach mit einem Loop zu lösen, der entweder fix die ersten 1500 Zeilen durchgeht und bei Bedarf angepasst wird oder eben vorher schaut wie weit Tabelleneinträge vorhanden sind.

Allerdings wäre mein Vorschlag das Du dich da etwas mit VBA beschäftigst.
Ein paar Variablen definierst, sodass du sie auch verstehst, das hilft am Anfang ungemein und eine Do-Loop Anweisung zu schreiben ist nicht wirklich schwierig.

 

[RoboMarvin]

Moin blackraven,

Ich würde nicht irgendwo Code "klauen" wenn ich die Grundlagen nicht kenne.

Das ist auch klug, daß du das nicht tust.

Allerdings wäre mein Vorschlag das Du dich da etwas mit VBA beschäftigst.

Ja, das werde bzw. mache ich bereits
Irgendwo muss man anfangen - und da ich bisher ohne VBA klar kam, ist es nun leider etwas komplexer.

Aber danke für's statement

 

[blackraven]

Und was in dem Code ist eigentlich falsch, wenn ich beim drücken von F5 "dieses" Fenster erhalte??
Dieses Mal war ich NICHT im Modul, sondern auf Tabelle 1

Liegt an der "privat" Deklaration des Sub.

 

[Scientist]

Die Idee mit Zeile() war:
In Spalte A in Tabelle1 wird die Zeilennummer eingetragen statt eine fortlaufende Zahl ("ZEILE()" statt "MAX()") und in Tabelle3 wird mit "KKLEINSTE()" die Position in Spalte A bestimmt.
Wenn aber eine Zeile in Tabelle1 entfernt wird, wird auch eine Position in Spalte A nicht mehr dargestellt und die manuellen Eintraege in Tabelle3 passen nicht mehr zum Rest.

Zum Problem:
Ich dachte, um das entfernen von Zeilen in Tabelle1 dreht sich allgemein das Problem?
Ansonsten funktioniert doch auch der bestehende Ansatz?

Mit direkt einfuegen meine ich, dass bei Eingabe in Spalte F in Tabelle1 die Eingabe direkt in Tabelle3 mithilfe eines Makros uebertragen wird und nicht erst ueber drei Ecken.


Zum Problem beim Ausfuehren:
Ich lasse mich gerne eines besseres belehren, aber meines wissens nach, lassen sich Events nicht manuell ausloesen.
Zum Testen wuerde ich ein Haltepunkt setzen und das Event ausloesen oder den Inhalt erst in einer normalen Funktion testen.

 

[RoboMarvin]

Zum Problem:
Ich dachte, um das entfernen von Zeilen in Tabelle1 dreht sich allgemein das Problem?
Ansonsten funktioniert doch auch der bestehende Ansatz?

Wie gesagt, ich könnte mich auch damit anfreunden nix zu löschen sondern ggf nur auszublenden.
Funktioniert so aber immer noch nicht, da sich die Positionen (A) ja verändern, die Chronologie quasi nicht erhalten bleibt.
und wenn du das "direkte Eintragen" in Tab. 3 mittels makro meinst; füllt es die Tab. 3 dann von oben Zeile für Zeile oder übernimmt es dann ähnliche Zeilen-Positionen wie in der Tab.1??

Danke again!
Flo


PS: Wenn das alles so ein Aggerwaaz ist, müssen sonst einfach die Positionen in Tab.1 A manuell eingegeben werden.
Ich wollte halt für die Kollegen was erstellen, mit möglichst wenig manuellen Energieaufwand - da hängen aber keine Leben von ab.

 

[Scientist]

Wie man die Tabelle3 mittels Makro fuellt, bleibt dir ueberlassen.
Man kann sie auch zufaellig fuellen, ist nur nicht sinnlos

Ansonsten diskutieren wir aktuell nur moegliche Loesungen und Ansaetze, die Umsetzung vom bisher besprochenen ist nichts neues und auch nicht kompliziert umzusetzen.

Ich persoenlich wuerde es mit VBA loesen, weil es absolute Eintraege sind und es so nicht durch nachtraegliche Aenderungen zu Fehlern kommen kann.
Dabei wuerde ich schlichtweg die Eingabe in Tabelle1 Spalte F abfangen (Change-Event in Spalte F), den letzten Eintrag in Tabelle3 Spalte B ermitteln (ermittelte Zeile plus 1) und dann die Daten von Tabelle1 in Tabelle3 kopieren.
Der ueberwiegende Teil des Codes steht sogar bereits hier im Thread.

 

[RoboMarvin]

Moin-moin,

@Scientist: vielen Dank für die Zeit die du dir wiedereinmal genommen hast.
Und auch die Tatsache, daß es deiner Meinung nach kein Hexenwerk ist, das gewünschte umzusetzen beruhigt mich.
Du schriebst:

Dabei wuerde ich schlichtweg die Eingabe in Tabelle1 Spalte F abfangen (Change-Event in Spalte F), den letzten Eintrag in Tabelle3 Spalte B ermitteln (ermittelte Zeile plus 1) und dann die Daten von Tabelle1 in Tabelle3 kopieren.
Der ueberwiegende Teil des Codes steht sogar bereits hier im Thread.

Nur bitte nochmal zur Berücksichtigung: Ich hatte noch NIE mit VBA zu tun... ...dürfte ich dich bitten (vorausgesetzt deine Zeit u. Lust lässt es zu) mir diesen Code vorzubereiten?

Falls nicht, ist es überhaupt kein Problem; dann würde ich jetzt kurzfristig die manuelle Lösung nehmen - und mich eventuell zukünftig mit VBA beschäftigen
Sollllltest du mir diesen Gefallen tun, könntest du dazuschreiben, an welcher Stelle der Code was macht?


Egal, wie du dich entscheidest: Viiiielen-vielen Dank, daß du mich bis hierhin betreut hast!!!

Flo

 

[blackraven]

Setzte in A10 die Formel "=MAX(A11:A1500)"
lösche sämtliche Formeln darunter aus Spalte A.


Dann sollte der Code deinen Zeck erfüllen.
Die Optimierung überlasse ich Dir.

Sub Makro1()

Dim counter, puffer As Integer
Application.ScreenUpdating = False

counter = 11

Sheets("Baukosten").Select
Range("A10").Select
puffer = ActiveCell


Do

Range("F" & counter).Select

If ActiveCell = "" Then
Else
    Range("A" & counter).Select
        If ActiveCell = "" Then
        ActiveCell = puffer + 1
        puffer = puffer + 1
        Else
        End If
End If

counter = counter + 1
Loop Until counter = 1200

Application.ScreenUpdating = True
End Sub

 

[Scientist]

Das ist natuerlich dann eine Einbahnstraße.
Wenn Zeilen in Tabelle1 geloescht werden, bleiben die Eintraege in Tabelle3 enthalten.
Was vielleicht auch besser so ist. Wenn an der Stelle ein Fehler passiert, gehen Informationen verloren.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim letztezeile1 As Long, letztezeile3 As Long
    Dim RaBereich As Range, RaZelle As Range
   
    On Error GoTo Ende
   
    ' https://www.excel-inside.de/beispiele_vba/vba-zellen-bereiche/337-letzte-zeile-letzte-spalte-und-letzte-zelle-per-vba-ermitteln
    letztezeile1 = Tabelle1.Cells(Rows.Count, 4).End(xlUp).Row
   
    ' Setze/definiere einen Referenzbereich
    Set RaBereich = Range(Cells(11, 6), Cells(letztezeile1, 6))
   
    ' Vergleich Schnittmenge zweier Bereiche
    ' Vergleich bearbeitete/r Zelle/Zellbereich in Referenzbereich
    Set RaBereich = Intersect(Range(Target.Address), RaBereich)
   
    ' Wenn Schnittmenge nicht leer, weiter
    If Not RaBereich Is Nothing Then
        ' Schleife über alle bearbeiteten Zellen innerhalb des Referenzbereichs
        For Each RaZelle In RaBereich
            ' Wenn aktuell betrachtete Zelle nicht leer ist, weiter
            If Not RaZelle.Value = "" Then
           
                letztezeile3 = Tabelle3.Cells(Rows.Count, 2).End(xlUp).Row + 1
               
                ' kopiere Daten von Tabelle1 nach Tabelle3
                Tabelle3.Cells(letztezeile3, 2).Value = Tabelle1.Cells(RaZelle.Row, 6).Value
                Tabelle3.Cells(letztezeile3, 3).Value = Tabelle1.Cells(RaZelle.Row, 4).Value
            End If
        Next RaZelle
    End If
   
Ende:
    MsgBox ("Änderung konnte nicht übertragen werden")
End Sub

 

[RoboMarvin]

...alter Schwede...!
DAS ist auf jedenfalls etwas, was man sich bestimmt mal nicht "eben" aneignet.

Vielen-vielen-vielen lieben Dank (an euch beide!!)

Wie toll, Scientist, dass du dir auch noch die Mühe gemacht hast, mir zu beschreiben was an welcher Stelle passiert. 1000 Dank!

Das werde ich diese Woche noch ausprobieren und Freitag oder kommenden Montag Rückmeldung geben.

Zufällig jemand nahe Kiel?? Dann würd ich mich mitm Flens und nem Fischbrötchen revangieren


Schönen Abend zusammen
Flo