VBA suchen und einfügen anderes Tabellenblatt

Moderator: ModerationP

VBA suchen und einfügen anderes Tabellenblatt

Beitragvon BlackPanda » 16. Sep 2021, 12:03

Hallo liebe Community,

ich würde gerne eine Personalliste erstellen. Die Idee ist es Krankheitsvertretungen in eine laufende Personalliste einzufügen.
Im Tabellenblatt "Personalliste" werden alle Teams und Mitarbeiter aufgelistet sein. Nachname, Vorname und FS (Führerschein)
Im Tabellenblatt "Befristungen" werden die Krankheitsvertretungen eingetragen.

Per VBA möchte ich gerne Mustermann Günter aus Tabellenblatt "Befristungen" im Tabellenblatt "Personalliste" suchen und per offset Musterfrau Paul aus "Befristungen" in der Personalliste einfügen.

Sodass in der Personalliste dann folgendes steht:

Mustermann Günter B Musterfrau Paul C

Hoffe ich konnte es gut erklären ^^

Freue mich über Anregungen.

Gruß
Florian
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
BlackPanda
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 219
Registriert: 14. Sep 2015, 12:55

Re: VBA suchen und einfügen anderes Tabellenblatt

Beitragvon BlackPanda » 21. Sep 2021, 08:03

Hallo liebes Forum,

ich versuche es noch mal :-)
Ich suche nach einer Lösung einen Namen und Vornamen auf zwei Tabellenblätter zu Vergleichen. Also im Tabellenblatt 2 steht ein Name und ein Vorname. Es soll nun überprüft werden, ob es im Tabellenblatt 1 ebenfalls diesen Namen und Vornamen gibt.
Falls es einen Treffer gibt, sollen die Werte die im Tabellenblatt 2 links neben dem Namen und Vornamen, der Name und Vorname der Vertretung im Tabellenblatt 1, rechts neben dem Namen Vornamen und FS eingetragen werden.
Ich hoffe es gibt da etwas passendes. In der Suche habe ich leider nichts gefunden.

Gruß
Florian
BlackPanda
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 219
Registriert: 14. Sep 2015, 12:55

Re: VBA suchen und einfügen anderes Tabellenblatt

Beitragvon HKindler » 21. Sep 2021, 09:45

Hi,

wozu VBA? Das kann man doch ganz bequem mit Formeln regeln. Und das sage ich ich als absoluter Fan von VBA! Aber manche Dinge sind mit Formeln wesentlich einfacher zu regeln.

Füge in Befristungen die Hilfsspalte K ein mit der Formel =D5&"|"&E5 in K5. Diese ziehst du runter bis K37.

In Personallistekommt in P15 die Formel =VERGLEICH(J15&"|"&K15;Befristungen!$K$5:$K$37;0).
Jetzt noch in M15 die Formel =INDEX(Befristungen!A$5:A$37;$P15), die du nach rechts bis O15 ziehst. M15:P15 ziehst du nach oben und unten.

Wenn du aus deinen Tabellen noch intelligente Tabellen machst, brauchst du dich um die Anpassung der Bereiche nicht zu kümmern.
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6463
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: VBA suchen und einfügen anderes Tabellenblatt

Beitragvon BlackPanda » 21. Sep 2021, 10:27

Hallo Helmut,

vielen Dank für deinen Beitrag. Darüber habe ich ehrlich gesagt nicht nachgedacht. Stimmt eigentlich und dein Lösungsansatz ist gut! Danke dafür.
Meine Idee war noch das ich, wenn es einen Treffer gibt, in Personalliste die 3 Werte tausche. Sprich der Vertretene in der Personalliste nach rechts rückt und die Krankheitsvertretung nach links.
Dazu habe ich folgendes Makro, was auch funktioniert.

Code: Alles auswählen
Sub tauschen()
Dim c As Range
Dim w1 As Variant, w2 As Variant, w3 As Variant, w4 As Variant, w5 As Variant, w6 As Variant
Dim i As Long, a As Long

    For Each c In Selection
      a = a + 1
    Next c
    If a <> 6 Then
      MsgBox "Die Anzahl der markierten Zellen ist nicht sechs !" & Chr(10) & _
        "Das Makro wird abgebrochen !", , _
        "Dezenter Hinweis für " & Application.UserName & ":"
      Exit Sub
    End If
    a = 0
    For Each c In Selection
      a = a + 1
      If a = 1 Then w1 = c.Value
      If a = 2 Then w2 = c.Value
      If a = 3 Then w3 = c.Value
      If a = 4 Then w4 = c.Value
      If a = 5 Then w5 = c.Value
      If a = 6 Then w6 = c.Value
    Next c
    a = 0
    For Each c In Selection
      a = a + 1
      If a = 1 Then c.Value = w4
      If a = 2 Then c.Value = w5
      If a = 3 Then c.Value = w6
      If a = 4 Then c.Value = w1
      If a = 5 Then c.Value = w2
      If a = 6 Then c.Value = w3
    Next c
End Sub


Nur wenn ich den Vergleich über Formeln mache, übernimmt er zwar die Namen, aber die Formeln sind weg. Und ich muss mir noch was einfallen lassen, wenn der erkrankte wieder zurück kommt. Das muss also auch wieder Rückgängig gemacht werden können. ^^

Gruß
Florian
BlackPanda
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 219
Registriert: 14. Sep 2015, 12:55

Re: VBA suchen und einfügen anderes Tabellenblatt

Beitragvon HKindler » 21. Sep 2021, 11:25

Hi Florian,

dein Tauschen geht auch einfacher:
Code: Alles auswählen
Sub tauschen()
Dim tmp As Variant
dim i As Long
i = Selection.Row
tmp = Range("J" & i & ":L" & i).Value
Range("J" & i & ":L" & i).Value = Range("M" & i & ":O" & i).Value
Range("M" & i & ":O" & i).Value = tmp
End Sub

Oder noch kompakter
Code: Alles auswählen
Sub tauschen()
Dim tmp As Variant
With Cells(Selection.Row, "J").Resize(, 3)
    tmp = .Value
    .Value = .Offset(, 3)
    .Offset(, 3) = tmp
End With
End Sub
Hier werden J:L und M:O der ersten Zeile des selektierten Bereichs getauscht. Es reicht also irgendeine Zelle der gewünschten Zeile anzuklicken und dann das Makro aufzurufen.


Wenn du unbedingt auf einer VBA-Lösung bestehst, dann behalte die Hilfsspalten in beiden Blättern bei. Und nun machst du ein weiteres Makro, das in der aktuellen Zeile die Vertretung einträgt:
Code: Alles auswählen
Sub Vertretung()
With Cells(Selection.Row, "M").Resize(, 3)
    .FormulaR1C1 = "=INDEX(Befristungen!C[-12],RC16)"
    .Value = .Value
End With
End Sub
Wieso weiter mit Hilfsspalten? Weil es mir zu blöd ist das Suchen per VBA nachzubilden.

Übrigens alles ungetestet, da du ein VBA-Passwort gesetzt hast.

PS: Ich finde es ganz schön verstörend, dass du ein Makro haben willst, aber eine Exceldatei als Beispiel zu Verfügung stellst, bei der die Makros per Passwort geschützt sind. Nicht dass ich das nicht umgehen könnte, wenn ich wollte, aber wenn ich mir dein hier gezeigtes Makro anschaue, lohnt sich der Aufwand nicht.
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office 365 ProPlus (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6463
Registriert: 04. Jul 2013, 09:02
Wohnort: Schwarzwald

Re: VBA suchen und einfügen anderes Tabellenblatt

Beitragvon BlackPanda » 21. Sep 2021, 11:33

Hi,

entschuldige, dass Passwort hatte ich vergessen raus zu nehmen. Ich hatte mich zu sehr drauf konzentriert, dass ich keine Personenbezogenendaten vergesse raus zu löschen.
Ich probiere deine Code gerne einmal aus.

Vielen Dank.

Gruß
Florian
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
BlackPanda
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 219
Registriert: 14. Sep 2015, 12:55


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 11 Gäste