Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1848to1852
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

@ Rolf, neopa C: Formel in VBA umwandeln

@ Rolf, neopa C: Formel in VBA umwandeln
21.09.2021 18:11:33
Stephan
Hallo Rolf, du hattest mir Anfang des Monats schon mit einer Formel geholfen, neopa C hatte noch kommentiert.
Kann die Formel evtl. in einen VBA Code umgewandelt werden?
Während der Verwendung merke ich jetzt, dass das sinnvoller ist.
https://www.herber.de/bbs/user/148190.xlsm
Kurz erklärt:
In B5:E25 und B29:E49 stehen Personeneinteilungen: Datum, Zeit, Name Ort
Diese sollen als Übersicht in I23:AF36 wiedergegeben werden (die meisten Namen, nicht alle)
In der Beispieltabelle sind 2 Monate angezeigt.
Problem bei der aktuellen Formel in I23:AF36 ist: Sobald die Einteilung in B5:E25 und B29:E49 weg ist, weil sie durch neue Monate überschrieben wird, sind auch die Werte weg... Hab ich nicht richtig bedacht...
Als Zwischenlösung habe ich die Eintragungen nach unten kopiert (ab B52), um sie in der Übersicht zu erhalten.
Kann ein Makro das Datum aus B nach I23:AF36 zu den entsprechenden Namen rüberkopieren und so wie jetzt an den richtigen Stellen anzeigen? (nur Werte einfügen, damit die Hintergrundfarbe nicht verlorengeht)?
Ziel ist, dass die Werte in I:AF erhalten bleiben, auch wenn B5:E25 und B29:E49 erneuert werden.
Vielen Dank, Gruß Stephan

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: @ Rolf, neopa C: Formel in VBA umwandeln
21.09.2021 18:58:46
Rolf
Hallo Stephan,
am einfachsten, (da wir die Formel ja schon haben) per VBA als lokale Formel einfügen und dann durch die Werte ersetzen.

Sub Formel_ersetzen()
Dim Formel As String
Formel = "=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE($B$4:$B$250)/($D$4:$D$250=$G23)/(TEXT($B$4:$B$250;""MMM"")=I$22);1));"""")"
With Worksheets("Planung").Range("I23:AF36")
.FormulaLocal = Formel
.Value = .Value
End With
End Sub
Das Makro rufst Du dann auf wann es passt.
Gruß Rolf
AW: @ Rolf, neopa C: Formel in VBA umwandeln
21.09.2021 19:14:27
Stephan
Hallo Rolf, danke.
Ich habe den Code als neues Modul eingefügt. Korrekt? Oder was ist mit "lokale Formel" und "Werte ersetzen" gemeint?
Wenn ich das Makro ausführe, werden die Werte korrekt rüberkopiert, aber wenn ich in B:D dann neue Daten eingebe und diese wieder rüberkopiere, werden die vorherigen in I:AF gelöscht.
Hab ich was falsch gemacht?
Anzeige
AW: @ Rolf, neopa C: Formel in VBA umwandeln
21.09.2021 19:57:03
Rolf
Hallo Stephan,
nein, ICH hab was falsch gemacht!
Ich hatte vergessen, dass es ja zwei verschiedene Formeln waren. Im Makro wird immer die selbe Formel eingefügt.
so sollte es richtig sein:
Sub Formel_ersetzen()
Dim Formel1 As String
Dim Formel2 As String
Dim Aktive As String
Aktive = ActiveCell.Address
Formel1 = "=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE($B$4:$B$250)/($D$4:$D$250=$G23)/(TEXT($B$4:$B$250;""MMM"")=I$22);1));"""")"
Formel2 = "=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE($B$4:$B$250)/($D$4:$D$250=$G23)/(TEXT($B$4:$B$250;""MMM"")=I$22);2));"""")"
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Planung")
.Range("I23:I36").FormulaLocal = Formel1
.Range("J23:J36").FormulaLocal = Formel2
.Range("I23:J36").Copy
.Range("K23:AF36").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Range("I23:AF36").Value = .Range("I23:AF36").Value
End With
Application.CutCopyMode = False
Application.EnableEvents = True
Range(Aktive).Select
End Sub Wenn Du jetzt noch in das Worksheet_Change Event Deiner Tabelle 'Planung' folgendes Makro kopierst, wird die Tabelle nach jeder Änderung in Spalte B/D aktualisiert.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B4:E200")) Is Nothing Then Exit Sub   'Bereich evtl. anpassen
If Cells(Target.Row, 2) = "" Or Cells(Target.Row, 4) = "" Then Exit Sub
Call Formel_ersetzen
End Sub
Gruß Rolf
Anzeige
AW: @ Rolf, neopa C: Formel in VBA umwandeln
21.09.2021 21:54:34
Stephan
ok, die automatische Aktualisierung erfolgt.
Aber: wenn ich in B:D die Inhalte gelöscht habe und neue Datumswerte eingebe, wird auch in I:AF das Datum gelöscht, sobald ich in D einen Namen eingebe.
Ich bekomme also immer nur 2 Monate in I:AF angezeigt.
Hab die Datei mit deinen beschriebenen Codes nochmal angehängt, schau doch bitte mal rein.
Eilt nicht. Danke
https://www.herber.de/bbs/user/148194.xlsm
AW: @ Rolf, neopa C: Formel in VBA umwandeln
22.09.2021 21:54:25
Rolf
Hallo Stephan,
...das hättest Du auch etwas früher erwähnen können, dass Du die Werte der anderen Monate erhalten willst.
Dadurch macht die Umsetzung der Formeln auf VBA keinen Sinn mehr.
Dann besser jede Änderung, ohne jegliche Formel, direkt per VBA in die Übersicht eintragen lassen:
in ein allgemeines Modul:

Private Sub Datum_eintragen(Zeile)
Dim iCol As Integer
Dim rng As Range
Dim iRow As Integer
Set rng = Range("I22:AF22").Find(What:=Format(Cells(Zeile, 2), "MMM"), LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
iCol = rng.Column
Else
Exit Sub
End If
Set rng = Range("G23:G36").Find(What:=Cells(Zeile, 4), LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
iRow = rng.Row
Else
Exit Sub
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
If Cells(iRow, iCol) = "" Then
Cells(iRow, iCol) = Cells(Zeile, 2)
ElseIf Cells(iRow, iCol + 1) = "" Then
Cells(iRow, iCol + 1) = Cells(Zeile, 2)
Else
MsgBox "Name wird gelöscht!", vbOKOnly, "Dreifachbelegung!"
Cells(Zeile, 4).ClearContents
End If
Application.EnableEvents = True
End Sub
ins Worksheet_Change Ereignis von Planung:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5:E49,A29:E49")) Is Nothing Then Exit Sub   'Bereich evtl. anpassen
If Cells(Target.Row, 1) = "" Or Cells(Target.Row, 4) = "" Then Exit Sub
Call Datum_eintragen(Target.Row)
End Sub
Damit wird jedes neue Datum in den entsprechenden Monat in der Übersicht eingetragen.
Gruß Rolf
Anzeige
AW: @ Rolf, neopa C: Formel in VBA umwandeln
22.09.2021 21:58:54
Stephan
ok, danke, ich probiers morgen am PC aus.
entschuldige bitte, ich dachte, ich hätte es genau genug formuliert, dass vorherige Werte erhalten bleiben sollen. Falls nicht, tut es mir leid :-)
AW: @ Rolf, neopa C: Formel in VBA umwandeln
23.09.2021 18:56:41
Stephan
Hallo Rolf,
ich hab den 1. Code in ein Modul eingetragen und den 2. unter Worksheet_Change
Beim Eintragen eines Datums in der Tabelle kommt jetzt im Worksheet_Change:
Fehler beim Kompilieren: Sub oder Function nicht definiert
und "Call Datum_eintragen" wird markiert.
AW: @ Rolf, neopa C: Formel in VBA umwandeln
23.09.2021 19:38:13
Rolf
Hallo Stephan,
entferne mal aus der
Private Sub Datum_eintragen(Zeile)
das Private, also nur
Sub Datum_eintragen(Zeile)
..weiß nicht, wie das da reingerutscht ist.
Gruß Rolf
Anzeige
AW: @ Rolf, neopa C: Formel in VBA umwandeln
23.09.2021 19:55:16
Stephan
jetzt ist es perfekt, vielen Dank
Bitteschön und Danke für die Rückmeldung. owT
23.09.2021 20:13:54
Rolf

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige