Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1844to1848
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

Feste Zellen ansprechen

Feste Zellen ansprechen
20.08.2021 16:24:48
Andre
Hallo VBA-Experten,
ich habe einen Code geschrieben der wunderbar funktioniert, aber auch extrem gefährlich werden kann und ich brauche eure Hilfe, diesen so zu optimieren, dass kein Risiko von Datenverlust entsteht.
Ich habe eine Arbeitsmappe mit 2 Blättern. Viele Einträge müssen auf beiden stehen und wenn Daten auf dem 1. Blatt verändert werden oder eingetragen, sollen sie im 2. Blatt automatisch aktualisiert werden. Mein Code überprüft quasi meine Spalte A auf Tabellenblatt 1 und wenn in Tabellenblatt 2 in Spalte C derselbe Eintrag steht, überträgt er mir einige Zellen von Tabellenblatt 1 auf 2 auf. Ich habe folgenden Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Range("C40:C400")) Is Nothing) Then 'Wenn Spalte C aktiviert dann
Dim A As Long
With Sheets("Tabelle1")
For A = .Cells(65536, 1).End(xlUp).Row To 1 Step -1
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 4).Value = .Cells(A, 3)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 5).Value = .Cells(A, 7)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 6).Value = .Cells(A, 9)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 8).Value = .Cells(A, 66)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 9).Value = .Cells(A, 65)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 10).Value = .Cells(A, 71)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 11).Value = .Cells(A, 70)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 12).Value = .Cells(A, 67)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 13).Value = .Cells(A, 68)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 14).Value = .Cells(A, 74)
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then Worksheets("Tabelle2").Cells(ActiveCell.Row, 15).Value = .Cells(A, 69)
Next A
End With
End If
End Sub
Mein Problem ist nun, dass dieser Code sich auf feste Spalten bezieht. Sollte ein Mitarbeiter irgendwo eine Spalte einfügen, merkt der Code das nicht und überschreibt plötzlich falsche Daten. Im normalen Excel setzt man das ja mit $-Zeichen um, wie könnte ich das hier in meinen VBA Code integrieren? Ich habe versucht mit dem Namensmanager zu arbeiten und anstelle der Spalten versucht Namen anzusprechen, das habe ich leider nicht zum Funktionieren bekommen.
Ich bin über jede Hilfe dankbar!
Viele Grüße, Andre

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Feste Zellen ansprechen
20.08.2021 17:00:47
ChrisL
Hi Andre
Nur mal um aufzuzeigen, wie du den Spalten-Index von definierten Namen ansprechen kannst.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (Intersect(Target, Range("C40:C400")) Is Nothing) Then 'Wenn Spalte C aktiviert dann
Dim A As Long
With Sheets("Tabelle1")
For A = 1 To .Cells(65536, 1).End(xlUp).Row
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then
Worksheets("Tabelle2").Cells(ActiveCell.Row, Worksheets("Tabelle2").Range("TB2_Name1").Column).Value = .Cells(A, .Range("TB1_Name1").Column)
Worksheets("Tabelle2").Cells(ActiveCell.Row, Worksheets("Tabelle2").Range("TB2_Name2").Column).Value = .Cells(A, .Range("TB1_Name2").Column)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 6).Value = .Cells(A, 9)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 8).Value = .Cells(A, 66)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 9).Value = .Cells(A, 65)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 10).Value = .Cells(A, 71)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 11).Value = .Cells(A, 70)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 12).Value = .Cells(A, 67)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 13).Value = .Cells(A, 68)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 14).Value = .Cells(A, 74)
'            Worksheets("Tabelle2").Cells(ActiveCell.Row, 15).Value = .Cells(A, 69)
End If
Next A
End With
End If
End Sub
cu
Chris
Anzeige
AW: Feste Zellen ansprechen
20.08.2021 17:01:39
Werner
Hallo,
so hast du in der Variablen i die Spalte deines Benannten Bereiches, die du dann im weiteren Code verwenden kannst.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim A As Long, i As Long
If Not Intersect(Target, Range("DerBereichsname")) Is Nothing Then
i = Range("DerBereichsname").Column
MsgBox i
End If
End Sub
Gruß Werner
AW: Feste Zellen ansprechen
20.08.2021 17:34:29
onur
Ich verstehe nicht, was du mit "wunderbar funktioniert" meinst, denn dein Code (ich nehme an, er steht im Modul von Tabellenblatt 2) macht folgendes:
Sobald du auf eine Zelle (z.B. C40) im Bereich Range("C40:C400") klickst (warum beim Klicken und nicht bei Änderung?), passiert Folgendes:
C40 wird in einer Schleife mit SÄMTLICHEN Zellen von A1 bis AEnde verglichen und jedes mal, wenn C40= Axy ist, wird kopiert, egal, wie oft das vorkommt und dadurch das letzte Kopieren überschrieben (wenn der Wert in C40 mehrfach in A vorkommt).
Ich weiss nicht, ob es genau das ist, was du eigentlich willst.
Ausserdem:
Du brauchst nicht 11 mal

If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then ...
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then ...
zu schreiben, wenn die Bedingung jedes mal exakt gleich ist, es reicht:

If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(ActiveCell.Row, 3) Then
TuEtwas
TuEtwas
End if

Anzeige
AW: Feste Zellen ansprechen
22.08.2021 17:21:09
Andre
Hallo onur,
auf Tabellenblatt 1 in Spalte A stehen Nummern (Bsp. 1-100). Jede nur genau 1x und ich möchte, dass wenn jemand auf Tabellenblatt 2, beispielsweise die 42 einträgt (hier stehen die Zahlen mehrfach), die passenden Werte bzw Text von Tabellenblatt 1 übernommen werden. Genau das macht dieser Code aktuell, deswegen finde ich die Formulierung "funktioniert" schon passend. Allerdings funktioniert er nur so lange, bis jemand eine Spalte einfügt, deswegen suche ich genau dafür eine Anpassung.. Ich bin kein Experte und gerade am Lernen, deswegen bin ich für jeden Tipp oder Optimierung dankbar.
"warum beim Klicken und nicht bei Änderung?" - Ich hätte es gerne bei Änderung, da fehlen mir aber die passenden Befehle für. Wenn du mir passende Änderung schreiben könntest, wäre ich dir dankbar.
Viele Grüße, Andre
Anzeige
AW: Feste Zellen ansprechen
22.08.2021 17:33:12
onur
Du kannst das, was du hast, genauso im Change_Event verwenden, statt im SelectionChange-Event.
Übrigens brauchst du kein ActiveCell, denn das ist ja TARGET.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C40:C400")) Is Nothing Then Exit Sub
Dim A As Long
With Sheets("Tabelle1")
For A = .Cells(65536, 1).End(xlUp).Row To 1 Step -1
If .Cells(A, 1).Value = Worksheets("Tabelle2").Cells(Target.Row, 3) Then
Worksheets("Tabelle2").Cells(Target.Row, 4).Value = .Cells(A, 3)
Worksheets("Tabelle2").Cells(Target.Row, 5).Value = .Cells(A, 7)
End If
Next A
End With
End Sub

Anzeige
AW: Feste Zellen ansprechen
22.08.2021 17:51:13
Andre
Danke, das probiere ich morgen mal direkt so. Hast du auch noch einen Tipp, wie man die Spalten anders ansprechen kann als mit der Spaltennummer 3,4,7 ? Um diese Problematik zu umgehen, dass es nicht mehr funktioniert, wenn jemand eine Spalte einfügt?
AW: Feste Zellen ansprechen
22.08.2021 17:55:04
onur
Das Problem hat nix mit dem Ansprechen zu tun, denn normalerweise hat Niemand, sobald die Datei einmal steht, daran etwas herumzuändern.
Das kannst du mit Blattschutz verhindern.
Tip: alle Beiträge lesen. o.w.T.
23.08.2021 22:49:01
Werner

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige