VBA: Range über Kombination zweier Cells() festlegen

Andreas Eibach

unread,

May 13, 2010, 8:51:16 AM5/13/10

to

Hi,

es scheint ja tatsächlich dass es keine Extra-NG " .programming" für die
Excel-Entwickler gibt, dann schreib ich's halt mal hier rein.
Gegeben sind zwei ineinandergeschachtelte For's, Schleifenzähler sind i + k,
und laut eines Beispiels in der Online-Dokumentation *soll* das ja
eigentlich gehen:

quelle.Range(Cells(i, 1), Cells(i, 3)).Copy
Destination:=ActiveSheet.Range(Cells(k, 1), Cells(k, 3))

Also auf deutsch:
Kopiere in Arbeitsblatt 'quelle' in Zeile i die 1. bis 3. Spalte aufs
aktuelle AB, Zeile k, ebenfalls 1. bis 3. Spalte.
Wie realisiere ich das ohne umständlich eine For-Schleife bemühen zu müssen?
(idealerweise möchte ich auch i + k beibehalten und nicht in
Buchstaben-Spaltennamen umrechnen müssen, wenn's ginge)

-Andreas

Claus Busch

unread,

May 13, 2010, 9:20:26 AM5/13/10

to

Hallo Andreas,

Am Thu, 13 May 2010 14:51:16 +0200 schrieb Andreas Eibach:

> Also auf deutsch:
> Kopiere in Arbeitsblatt 'quelle' in Zeile i die 1. bis 3. Spalte aufs
> aktuelle AB, Zeile k, ebenfalls 1. bis 3. Spalte.

> Wie realisiere ich das ohne umst�ndlich eine For-Schleife bem�hen zu m�ssen?
> (idealerweise m�chte ich auch i + k beibehalten und nicht in
> Buchstaben-Spaltennamen umrechnen m�ssen, wenn's ginge)

warum kopierst du Zeile f�r Zeile? Kopiere doch den kompletten Bereich:

Sub Kopieren()

Dim LRow As Long

With Sheets("quelle")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Range(.Cells(1, 1), .Cells(LRow, 3)).Copy _
Destination:=ActiveSheet.Range("A1")
End With

End Sub

Mit freundlichen Gr�ssen
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Alexander Wolff

unread,

May 13, 2010, 10:53:52 AM5/13/10

to

Sub t()

i = 1
k = 4

'Claus' Loesung
'With Sheets("Tabelle1")
' .Range(.Cells(i, 1), .Cells(i, 3)).Copy _
' Destination:=ActiveSheet.Cells(k, 1)
'End With

With Sheets("Tabelle1")
Range(.Cells(i, 1), .Cells(i, 3)).Copy _
Destination:=ActiveSheet.Cells(k, 1)
End With

End Sub

Erkl�rung:
1) Am Ziel ben�tigst du nur die linke obere Zelle, so dass "Range()"
entbehrlich ist.
2) Die beiden aufspannenden ".Cells()" ben�tigen jeweils das Worksheet.
Warum dort und nicht nur bei Range, wo sie entbehrlich sind? Weil man auch
einen 3D-Bereich definieren k�nnen muss.
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2

Peter Schleif

unread,

May 13, 2010, 11:23:55 AM5/13/10

to

Alexander Wolff schrieb am 13.05.2010 16:53 Uhr:
>
> With Sheets("Tabelle1")
> Range(.Cells(i, 1), .Cells(i, 3)).Copy _
> Destination:=ActiveSheet.Cells(k, 1)
> End With

Sheets("Tabelle1").Cells(i,1).Resize(,3).Copy ActiveSheet.Cells(k,1)

würde Range, With-Block und ein Cells sparen. Dafür kommt Resize dazu.

Peter

Andreas Eibach

unread,

May 13, 2010, 2:13:20 PM5/13/10

to

Genial!!
Vielen Dank, Peter _und_ Alexander :)

-Andreas

Andreas Eibach

unread,

May 13, 2010, 2:20:59 PM5/13/10

to

"Claus Busch" <claus...@t-online.de> wrote:
> warum kopierst du Zeile f�r Zeile? Kopiere doch den kompletten Bereich:
>
> Sub Kopieren()
>
> Dim LRow As Long
>
> With Sheets("quelle")
> LRow = .Cells(Rows.Count, 1).End(xlUp).Row
> .Range(.Cells(1, 1), .Cells(LRow, 3)).Copy _
> Destination:=ActiveSheet.Range("A1")
> End With

Clever gedacht, w�rde aber bei mir so nicht gehen.
Kannst du aber nicht wissen, da ich nat�rlich den ganzen, hier nicht ganz so
relevanten Filtercode-Kram weggelassen habe.
Ich kopiere ja _ausgew�hlte_ Zeilen von quelle.z2 bis quelle.last
*deswegen* bem�he ich ja das k.
(k <> i in den meisten F�llen!)
D. h. wenn der erst bei Zeile 27 das erste Teil-Match findet dann kopiert er
z. B. Zeile 27 (Quelle) auf Zeile 3 (Ziel).
Und das n�chste Match kann dann erst in Zeile 101 (Q.) sein, das geht dann
auf Zeile 4 (Z.) usw.

-Andreas