VBA 2 Zellen austauschen
Andreas Killer
Gibt es eigentlich einen Trick um 2 Zellen samt Inhalt und Formaten zu
tauschen?
Nur den Inhalt ist easy:
Temp = Cells(1, 1)
Cells(1, 1) = Cells(2, 2)
Cells(2, 2) = Temp
aber das ganze andere Farb-/Format-/Hyperlink-Zeugs?
Kann man nicht irgendwie ein "Set Temp = New Range" erzeugen und den
ganzen Krempel von Cells(1,1) nach Temp schieben?
Andreas.
Alexander Wolff
3 Vorgehensweisen sind Dir bekannt:
1. .Copy [Destination]
2. Das Einzel-Abklappern aller Eigenschaften, wie neulich von Dir gezeigt
(Bei den beiden sollte man evtl. jenseits aller Diskussion, ob die Methode
schön sei, mal einfach einen Performance-Test machen.)
3. Darüber hinaus von Dir vermutet oder gewünscht: Objektzuweisungen an
Zellen
Ich vermute, dass eine schöne Sub für 2. mit optionalen Argumenten mit
Default FALSE der richtige Weg wäre.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
thomas...@gmail.com
On 10 Mrz., 12:14, Andreas Killer <andreas.kil...@gmx.net> wrote:
> Juhu. :-)
Ebenso :-)
Ein Range ist immer ein Bezug zu einem Bereich in einem Tabellenblatt,
daher geht die temporäre Zuweisung an einen virtuellen Range nicht.
Ev. wäre es denkbar, ein temporäres Tabellenblatt als Scharnier zu
verwenden?
Ein möglicher Ansatz wäre folgender, der noch einiges an Ausbau
bedarf:
Sub tt()
SwitchCell Range("A3"), Range("C1")
End Sub
Public Sub SwitchCell(rngFirst As Range, rngSecond As Range)
Dim wsTemp As Worksheet
Dim rngSwitch As Range
Set wsTemp = Worksheets.Add
Set rngSwitch = wsTemp.Range("A1")
rngFirst.Copy rngSwitch
rngSecond.Copy rngFirst
rngSwitch.Copy rngSecond
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
End Sub
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für MS-Excel -
Andreas Killer
> > Kann man nicht irgendwie ein "Set Temp = New Range" erzeugen und den
> > ganzen Krempel von Cells(1,1) nach Temp schieben?
> Ein Range ist immer ein Bezug zu einem Bereich in einem Tabellenblatt,
> daher geht die temporäre Zuweisung an einen virtuellen Range nicht.
Sch...ade.
> Ev. wäre es denkbar, ein temporäres Tabellenblatt als Scharnier zu
> verwenden?
Hmm, ich kann mich dunkel erinnern das es da ein Limit bei der max.
Anzahl Sheets gibt, kann man die max. mögliche Anzahl irgendwo
abfragen?
Andreas.
Bernd P
Fuer einen "vollstaendigen" Tausch hinsichtlich Formeln, Format UND
FUER BEZUEGE ABHAENGIGER ZELLEN wuerde ich alle abhaengigen Excel
Files oeffnen und dann den Makrorecorder einschalten und
1. A1 selektieren und STRG + X
2. B1 selektieren und STRG + V
3. B2 selektieren und STRG + X
4. A1 selektieren und STRG + V
5. B1 selektieren und STRG + X
6. B2 selektieren und STRG + V
eingeben. Dann den Makrorecorder ausschalten, alle abhaengigen Excel
Files sichern und schliesslich den aufgenommenen Codesalat kuerzen.
Viele Gruesse,
Bernd
Andreas Killer
> Fuer einen "vollstaendigen" Tausch hinsichtlich Formeln, Format UND
> FUER BEZUEGE ABHAENGIGER ZELLEN wuerde ich alle abhaengigen Excel
> Files oeffnen und dann den Makrorecorder einschalten und
Danke für den Tip, aber der Hintergrund ist etwas komplizierter.
Ich habe mir ein AddIn geschrieben welches einen Zellbereich
transponiert, aber im Gegensatz zu der Excel-Transponierung kann es
die Daten an der *gleichen Stelle* transponieren und dabei auch
Teilbereiche größer als nur eine Zelle verarbeiten.
Damit kann man sehr easy aus mehrspaltigen Daten einspaltige machen
und umgekehrt, oder aus 3x2 spaltigen 2x3 spaltige, z.B. aus
a 1
b 2
c 3
das machen
a
1
b
2
c
3
oder aus
1a 1b 3a 3b 5a 5b 7a 7b
1c 1d 3c 3d 5c 5d 7c 7d
2a 2b 4a 4b 6a 6b 8a 8b
2c 2d 4c 4d 6c 6d 8c 8d
das
1a 1b 2a 2b
1c 1d 2c 2d
3a 3b 4a 4b
3c 3d 4c 4d
5a 5b 6a 6b
5c 5d 6c 6d
7a 7b 8a 8b
7c 7d 8c 8d
oder umgekehrt und vieles mehr. Bisher brauchte ich das immer nur auf
reine Daten, aber nun ist der Fall eingetreten das ich die Zellformate
mitnehmen möchte.
Und das möchte ich möglichst universell lösen.
Andreas.
Bernd P
Das ist nicht komplizierter, das ist einfacher.
Hast Du nicht bereits Deinen Algorithmus fuers Kopieren?
Dann nimm doch einfach das Format hinzu...
Viele Gruesse,
Bernd
Thomas Ramel
Andreas Killer schrieb am 11.03.2009
> On 10 Mrz., 14:55, thomas.ra...@gmail.com wrote:
>
>>> Kann man nicht irgendwie ein "Set Temp = New Range" erzeugen und den
>>> ganzen Krempel von Cells(1,1) nach Temp schieben?
>> Ein Range ist immer ein Bezug zu einem Bereich in einem Tabellenblatt,
>> daher geht die temporäre Zuweisung an einen virtuellen Range nicht.
> Sch...ade.
Ja, ist aber leider so.
>> Ev. wäre es denkbar, ein temporäres Tabellenblatt als Scharnier zu
>> verwenden?
> Hmm, ich kann mich dunkel erinnern das es da ein Limit bei der max.
> Anzahl Sheets gibt, kann man die max. mögliche Anzahl irgendwo
> abfragen?
Die Anzahl Blätter ist nur durch den vorhandenen Speicher beschränkt - in
aller Regel geht ein temporäres Blatt immer noch.
Wenn es - wie ich gerade gelesen habe - um dein AddIn geht, dann lege das
temporäre Blatt doch da drin an, dann wird auch die Original-Mappe in
keinster Weise verändert.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Vista Ultimate SP-1 / xl2007 SP-1]
Andreas Killer
> Wenn es - wie ich gerade gelesen habe - um dein AddIn geht, dann lege das
> temporäre Blatt doch da drin an, dann wird auch die Original-Mappe in
> keinster Weise verändert.
Manchmal hab ich einfach ein Brett vorm Kopf, super Idee, vielen Dank.
Andreas.
Alexander Wolff
Ich habe mir ein AddIn geschrieben welches einen Zellbereich
transponiert, aber im Gegensatz zu der Excel-Transponierung kann es
die Daten an der *gleichen Stelle* transponieren und dabei auch
Teilbereiche größer als nur eine Zelle verarbeiten.
--------------------------------------
Hi Andreas, das Thema ist nicht ganz trivial:
1. Anpassung der vier Bezugsarten, also rel, col-abs, row-abs, all-abs, in
jeder Formel einzeln notwendig, damit nach späterem Einfügen von Zeilen und
Spalten Formeln lückenvervollständigend dorthin kopiert werden können.
Achtung: Komplex! Problematisch wird es dann bei INDIREKT, ADRESSE,
INDEX:INDEX und anderen Funktionen mit Bezügen, die auf andere Weise als
direkt entstehen. Zur Beruhigung: Diese gehören eher selten in einen zu
transponierenden Bereich, da sie eher auswertenden Charakter haben, also
außerhalb stehen. - Auch Vektoren (erstellt man mit Einf Name Def) sind
schwierig anzupassen. Bei Bezügen auf Zellen im Transposebereich selbst kann
ein Reihenfolgeproblem entstehen, wenn nicht, wie früher empfohlen wurde,
Zellen sich nur auf andere Zellen links und/oder oben beziehen. Da Du die
Daten auch weitergehend als nur gesamt-transponierend neu anordnen können
möchtest, ergibt sich hier noch mehr Komplexität bis teilweise
Unmöglichkeit. Damit meine ich nicht, dass das Ergebnis nicht zustandekommen
kann, sondern, dass es dann Formeln enthält, bei denen man sich nachträglich
einen manuell richtigen Aufbau herbeisehnt.
2. Das Transponieren an Ort und Stelle geht am einfachsten mit einem
temporären Blatt. Am bezugerhaltungs-sichersten geht es wohl mit
Ausschneiden-Einfügen, jedoch mangelt es bei dessen Abschluß an der
Möglichkeit eines "Inhalte-Einfügens" (wie bei "Kopieren"). Kopierst Du
hingegen, braucht es Sorgfalt bei Referenzen aufs selbe Blatt:
Beispiel:
A2 sei die erste Zelle des Transpose, die auf sich selbst zurückkopiert
wird.
Tabelle1!A2: =A1 verschiebst Du nach Tabelle2. Dort steht dann:
Tabelle2!A2: =Tabelle1!A1. Nun verschiebst Du zurück:
Tabelle1!A2: =Tabelle1!A1. Was ist passiert? Auf einmal blatt-abs. Ref,
statt -rel.!
Das ist nach diesem ersten Transponieren noch nicht schlimm, aber wenn Du
die Formel einfach in ein neues Blatt kopieren möchtest, hast Du plötzlich
einen Bezug auf Tabelle1 statt auf "selbes Blatt". Daher muss bei dem
Fertigstellen des Transponierens mittels Ausschneidenmethode über temporäres
Hilfsblatt der Zustand blattloser Referenzen in der Formel, wo sie vorher so
waren, wiederhergestellt werden. Auch stören in der Lesbarkeit verbliebene
unnötige Tabelle!-Referenzen.
3. Es empfiehlt sich, im Dialogfenster "(x) Tabellensicherung vor Transpose"
vorzubelegen. Um die Schwierigkeiten zu 2. zu umgehen, könnte außerdem
vorbelegt sein: "Transponieren (x) über DA1 ( ) über A30001 ( ) temporäres
Blatt. Achtung: Bereiche werden überschrieben!", durch den User also
bestimmbar.