Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1240to1244
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

Korrekte Referenzierung, egal welche Tab. aktiv

Korrekte Referenzierung, egal welche Tab. aktiv
Peter

Guten Abend
Bei meinem Code
Sub Doppelte_Loeschen()
werden die "Doppelten" nur gelöscht, wenn auch  die Tabelle "Auswertung" ausgewählt ist. Wenn  _
eine andere Tabelle ausgewählt ist, wenn der Code abläuft, funktioniert das Löschen nicht.
Ich vermute, dass die Referenzierung bei der Zeile
'bei dieser Formel bleibt ein Null-Wert, sofern vorhanden
.FormulaR1C1 = "=IF(EXACT(RC[" & SP & "],R[-1]C[" & SP & "]),TRUE,RC[-1])"
nicht vollständig ist. Ich habe es jedoch nicht geschafft, diese zu ergänzen.
Wer kann mir helfen?
Gruss, Peter
With Sheets("Auswertung")
With .Range(.Cells(Z1, 2), .Cells(Z2, 2))
.EntireRow.Sort Key1:=Cells(Z1, SP + 2), Order1:=xlAscending, Header:=xlNo
''.FormulaR1C1 = "=IF(RC[" & SP & "]=R[-1]C[" & SP & "],TRUE,RC[-1])"       'bei dieser  _
Formel fliegen alle Null Werte raus
.FormulaR1C1 = "=IF(EXACT(RC[" & SP & "],R[-1]C[" & SP & "]),TRUE,RC[-1])"  'bei dieser  _
Formel bleibt ein Null-Wert, sofern vorhanden
.Formula = .Value
.EntireRow.Sort Key1:=Cells(Z1, 2), Order1:=xlAscending, Header:=xlNo
.SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
End With
End With

Sub Doppelte_Loeschen()
Dim Z1 As Long, Z2 As Long, SP As Long, c As Range, Ende As Long, lngSpa As Long, Bereich As  _
Range
Z1 = Range("psaBeginn").Row
Z2 = Range("psaEnde").Row
SP = Range("psaBeginn").Column
'--- Hilfsspalten einfügen und Original-Reihenfolge sichern
With Sheets("Auswertung")
.Range("A:B").Insert
With .Range(.Cells(Z1, 1), .Cells(Z2, 1))
.FormulaR1C1 = "=Row()"
.Formula = .Value
End With
End With
'--- Doppelte kennzeichnen und loeschen
On Error Resume Next
With Sheets("Auswertung")
With .Range(.Cells(Z1, 2), .Cells(Z2, 2))
.EntireRow.Sort Key1:=Cells(Z1, SP + 2), Order1:=xlAscending, Header:=xlNo
''.FormulaR1C1 = "=IF(RC[" & SP & "]=R[-1]C[" & SP & "],TRUE,RC[-1])"       'bei dieser  _
Formel fliegen alle Null Werte raus
.FormulaR1C1 = "=IF(EXACT(RC[" & SP & "],R[-1]C[" & SP & "]),TRUE,RC[-1])"  'bei dieser  _
Formel bleibt ein Null-Wert, sofern vorhanden
.Formula = .Value
.EntireRow.Sort Key1:=Cells(Z1, 2), Order1:=xlAscending, Header:=xlNo
.SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
End With
End With
On Error GoTo 0
'--- Aufräumen
Sheets("Auswertung").Range("A:B").Delete
'alle Adressnummern benennen (da möglicherweise durch das loeschen das "Ende" eliminiert  _
wurde
Ende = Sheets("Auswertung").Cells(65536, 1).End(xlUp).Row
Ende = WorksheetFunction.Max(Ende, 5)
Set Bereich = Worksheets("Auswertung").Range("A" & Range("psaBeginn").Row, "A" & Ende)
ActiveWorkbook.Names.Add _
Name:="psAdressen", _
RefersTo:=Bereich, Visible:=True
'letzte Zelle benennen
Set Bereich = Worksheets("Auswertung").Range("A" & Ende, "A" & Ende)
ActiveWorkbook.Names.Add _
Name:="psaEnde", _
RefersTo:=Bereich, Visible:=True
End Sub

AW: Korrekte Referenzierung, egal welche Tab. aktiv
10.12.2011 21:43:27
Gerd
Hallo Peter,
wenn es dein Problem ist:
'bei dieser Formel fliegen alle Null Werte raus
.FormulaR1C1 = "=IF(" & .Parent.Name & "!RC[" & SP & "]= " & .Parent.Name & "!R[-1]C[" & SP & "],TRUE," & .Parent.Name & "!RC[-1])"
'bei dieser Formel bleibt ein Null-Wert, sofern vorhanden
.FormulaR1C1 = "=IF(EXACT(" & .Parent.Name & "!RC[" & SP & "]," & .Parent.Name & "!R[-1]C[" & SP & "]),TRUE," & .Parent.Name & "!RC[-1])"
Gruß Gerd
AW: Korrekte Referenzierung, egal welche Tab. aktiv
11.12.2011 20:04:27
Peter
Hallo Gerd
Danke für die Antwort. Es hat sich jedoch gezeigt, dass das Problem nicht in diesen Zeilen lag und Franz hat mir jetzt glücklicherweise die Lösung gepostet.
Gruss, Peter
Anzeige
AW: Korrekte Referenzierung, egal welche Tab. aktiv
11.12.2011 12:52:21
fcs
Hallo Peter,
die Problemzeilen sind nicht die die Zeilen, in denen die Formeln im Blatt "Auswertung" eingefügt werden, sondern die Zeilen, in denen sortiert wird. Dort wird bei "Key1" auf eine Zelle im aktiven Blatt referenziert, statt auf eine Zelle im zu sortierenden Bereich.
Durch die Verwendung von
On Error Resume Next
bekommst du keinen Hinweis auf diesen Fehler.
Diese Zeile sollte man in einer Prozedur erst einfügen/aktivieren, wenn der Code funktioniert. Besser ist es eine konkrete Fehlerbehandlung einzubauen, die abhängig von den Fehler-Nummern den weiteren Ablauf steuert.
Gruß
Franz
Textdatei mit Code: https://www.herber.de/bbs/user/77923.txt
Anzeige
AW: Korrekte Referenzierung, egal welche Tab. aktiv
11.12.2011 20:13:00
Peter
Hallo Franz
Vielen Dank. Jetzt läufts problemlos.
Trotzdem ist mir noch nicht ganz klar, weshalb bei der Zeile
With Sheets("Auswertung")
.FormulaR1C1 = "=IF(EXACT(RC[" & SP & "],R[-1]C[" & SP & "]),TRUE,RC[-1])"
End With
nicht spezifiziert werden muss, auf welches Sheet sich diese Zellen bezieht (für den Fall, dass eben nicht die Tabelle "Auswertung" ausgewählt ist. Könntest du mir das noch erklären?
Du siehst, bei der Referenzierung habe ich noch immer meine liebe Mühe.
Nochmals Dankeschön und freudlicher Gruss, Peter
AW: Korrekte Referenzierung, egal welche Tab. aktiv
12.12.2011 01:30:43
fcs
Hallo Peter,
dann versuch ich mal dir das zu verklickern. Dabei muss du die gesamten With ... End With betrachten.
  With Sheets("Auswertung")
With .Range(.Cells(Z1, 2), .Cells(Z2, 2))
.EntireRow.Sort Key1:=.Parent.Cells(Z1, SP + 2), Order1:=xlAscending, Header:=xlNo
''.FormulaR1C1 = "=IF(RC[" & SP & "]=R[-1]C[" & SP & "],TRUE,RC[-1])"   'bei dieser _
Formel fliegen alle Null Werte raus
.FormulaR1C1 = "=IF(EXACT(RC[" & SP & "],R[-1]C[" & SP & "]),TRUE,RC[-1])"  'bei dieser _
Formel bleibt ein Null-Wert, sofern vorhanden
.Formula = .Value
.EntireRow.Sort Key1:=.Parent.Cells(Z1, 2), Order1:=xlAscending, Header:=xlNo
.SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
End With
End With

Für VBA ist ja wichtig, dass es erkennt auf welches Objekt sich eine gefragte Eigenschaft oder eine Methode beziehen soll.
Das Objekt kann man direkt angeben mit kompletter Referenz
z.B.
Workbooks("Mappe1.xls").Worksheets("Tabelle1").Cells(2,1).Value = "Test"
'oder in der aktiven Arbeitsmappe
Worksheets("Tabelle1").Cells(2,1).Value = "Test"
'oder im aktiven Tabellenblatt
Cells(2,1).Value = "Test"
Mit der With-Anweisung kann man das Bezugs-Objekt für einen längeren Code-Abschnitt festlegen.
Mit dem Punkt vor der Methode bzw. der Eigenschaft wird dann der Bezug zu dem Objekt in der oberhalb der Zeile stehenden With-Anweisung hergestellt.
Wird der Punkt weggelassen, dann beziehen sich in allgemeinen Modulen die Objekte immer auf das aktive übergeordnete Objekt.
Range(xxx) oder Cells(Zeile,Spalte) auf das aktive Blatt
Sheets("ABC") oder Worksheets("Tabelle1") auf die aktive Arbeitsmappe.
In deiner Frage ist ja entscheidend, dass die Zellen, in die die Formeln geschrieben werden sollen, korrekt referenziert werden. Das ist durch With-Anweisungen hier der Fall.
Der Formel-Text, der in die Zellen geschrieben wird, ist ja unabhängig davon, welches Tabellenblatt aktiv ist. Da sich die Formeln auf Zellen innerhalb des Tabellenblatts beziehen, in dem sie eingefügt werden, ist es nicht erforderlich das Tabellenblatt in den Formeln mit als Bezug anzugeben.
Eine Anweisung in der Form
      .FormulaR1C1 = "=IF(EXACT('" & .Parent.Name & "'!RC[" & SP & "],'" _
& .Parent.Name & "'!R[-1]C[" & SP & "]),TRUE,RC[-1])"  'bei dieser _
Formel bleibt ein Null-Wert, sofern vorhanden
ist hier nicht erforderlich.
Gruß
Franz
Anzeige
AW: Korrekte Referenzierung, egal welche Tab. aktiv
12.12.2011 06:54:58
Peter
Guten Tag Franz
Vielen Dank für die Erklärung. Das ist so sehr hilfreich.
Gruss, Peter

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige