Array ausgeben mit VBA

[Mama_Leone]

Hallo,

ich möchte ein Array mit Daten aus einem Blatt in einem zwieten Blatt ausgeben. Den Code für das Einlesen hätte ich schon mal, nur bei der For-Schleife zum Ausgeben hänge ich (siehe ?-Zeichen unten). Weiss jemand wie man das aufbauen müsste, damit die Daten aus dem Array in die Zellen des zweiten Blattes geschrieben werden. Eine Verständnisfrage zur Deklaration des Arrays Dim arr(100, 10) hätte ich noch: sind diese Nr. in der Klammer als max. verarbeitbare Menge anzusehen? Kann man die weglassen? Das habe ich mir von woanders abgeguckt ohne es richtig zu verstehen.

Gruss
ml

Sub daten()
Dim arr(100, 10)
Dim z As Integer
Dim s As Integer
Sheets(„Vorlage“).Activate
s = 2
Do While Cells(8, s) „“
z = 6
Do While Cells(z, 2) „“
arr(z, s) = Cells(z, s)
z = z + 1
Loop
s = s + 1
Loop
Sheets(„Test“).Activate
For ? = 1 To ?

Cells(z, s).Value = arr(z, s)

Next
End Sub

[Reinhard_fb8cb7]

Hi Mama,

ich möchte ein Array mit Daten aus einem Blatt in einem
zwieten Blatt ausgeben. Den Code für das Einlesen hätte ich
schon mal,

ja, gesehen, aber macht er auch das was du willst? Ich habe gewaltige Schwierigkeiten den Code zu verstehen, ich sehe schon was er macht bin mir aber unsicher ob du das willst was er macht.
Naja, nix Genaues weiß man halt nicht.

Achja, benutze den pre-Tag beim Posten vom Code, dann sieht das so aus sofern du Einrückungen benutzt, wenn nicht kannste auch pre weglassen

Der Code zeigt dir nacheinder den Inhalt von arr an (gut 0 hab ich unterschlagen)
Und zwar in dieser Reihenfolge:
arr(1,1)
arr(1,2)
arr(1…
arr(2,1)
arr(2,2)
usw.

Ist das das was du möchtest?

Sub daten()
Dim arr(100, 10), z As Long, s As Integer
Sheets("Vorlage").Activate
s = 2
Do While Cells(8, s) ""
 z = 6
 Do While Cells(z, 2) ""
 arr(z, s) = Cells(z, s)
 z = z + 1
 Loop
 s = s + 1
Loop
For z = 1 To UBound(arr, 1)
 For s = 1 To UBound(arr, 2)
 MsgBox arr(z, s)
 Next s
Next z
End Sub

nur bei der For-Schleife zum Ausgeben hänge ich
(siehe ?-Zeichen unten). Weiss jemand wie man das aufbauen
müsste, damit die Daten aus dem Array in die Zellen des
zweiten Blattes geschrieben werden.

Ja, aber ich weiß nicht welche Daten wohin.

Eine Verständnisfrage zur
Deklaration des Arrays Dim arr(100, 10) hätte ich noch: sind
diese Nr. in der Klammer als max. verarbeitbare Menge
anzusehen? Kann man die weglassen? Das habe ich mir von
woanders abgeguckt ohne es richtig zu verstehen.

Lese bitte in der Vba Hilfe zu Dim, Redim nach, danach frage nochmal nach was dann noch unklar ist.
Dim x ist eine Variable
Dim X(5) sind quasi 6 variablen
Dim x(100,10) sind quasi 1111 Variablen
Falls du denkst Dim(x(100,10) sind 1000 variablen dann lese bei Option Base nach

Gruß
Reinhard

[Mama_Leone]

Hallo Reinhardt,

das hat nicht ganz geklappt. Zum besseren Verständnis: es sollen Werte aus Blatt „Vorlage“, das bei B8 beginnt, 5 Spalten hat (also bis F) in ein Array eingelesen werden und in das Blatt Test ausgegeben werden. Die Anzahl der Zeilen ist im Blatt Vorlage unbekannt, da flexibel. Den Code habe ich ein wenig umgeschrieben. Es kommt zwar keine Fehlermeldung, aber es ist auch nicht sichtbar das was passiert. Daten werden jedenfalls nicht übertragen.

Gruss

Sub daten2()
Dim arr(100, 10), z As Long, s As Integer
Set tab1 = Worksheets("Vorlage")
Set Tab2 = Worksheets("Test")
s = 2
Do While tab1.Cells(8, s) ""
 z = 6
 Do While tab1.Cells(z, 2) ""
 arr(z, s) = tab1.Cells(z, s)
 z = z + 1
 Loop
 s = s + 1
Loop
For z = 1 To UBound(arr, 1)
 For s = 1 To UBound(arr, 2)
 Tab2.Cells(z, s).Value = arr(z, s)
 Next s
Next z
End Sub

[Reinhard_fb8cb7]

Danke für das t ml ,

das hat nicht ganz geklappt.

Was hat warum nicht geklappt, was geschah?

Zum besseren Verständnis: es
sollen Werte aus Blatt „Vorlage“, das bei B8 beginnt, 5
Spalten hat (also bis F) in ein Array eingelesen werden und in
das Blatt Test ausgegeben werden. Die Anzahl der Zeilen ist im
Blatt Vorlage unbekannt, da flexibel.

In welcher Spalte ist der letzte Wert der Spalte, in B?

Den Code habe ich ein wenig umgeschrieben.

Verstehe ich, war nötig *aargs, in den Tisch beiß* ich wollte doch nur rauskitzeln ob das Einlesen klappt *seufz*

Es kommt zwar keine Fehlermeldung,

Kann man ändern, welche Fehlermeldungen willste denn? *kicher*

aber
es ist auch nicht sichtbar das was passiert. Daten werden
jedenfalls nicht übertragen.

Daas kriegne wir schon, aber eher morgen, bin gleich aus dem Inet wech.

Frostern
Reinhard

[Mama_Leone]

Hallo Reinhardt,

eine Zeile war falsch gesetzt, der Code funktioniert doch. Drambardiner, Dein Code funktioniert auch. Etwas versuche ich noch anzupassen: Der Code überträgt die Daten in die Zieltabelle immer an die selbe Stelle wie er auch in der Vorlage steht. Es ändert auch nichts, wenn in der Zieltabelle vor dem Schreiben eine Zelle über Select ausgewählt wird. Ich denke, das liegt daran, dass in beiden Tabellen die selben Variablen verwendet werden. Unten im Code habe ich für die Zieltabelle andere Variablen genommen, leider passiert nichts. Was muss geändert werden, damit die Daten in der Zieltabelle an anderer Stelle geschrieben werden als in der Vorlage?

Gruss

Sub daten4()
Dim arr(100, 10), z As Long, s As Integer
Dim i As Long, j As Long
Set tab1 = Worksheets("Vorlage")
Set tab2 = Worksheets("Test")
s = 2
Do While tab1.Cells(8, s) ""
 z = 7
 Do While tab1.Cells(z, 2) ""
 arr(z, s) = tab1.Cells(z, s)
 z = z + 1
 Loop
 s = s + 1
Loop
i = 2
j = 2
For z = 1 To UBound(arr, 1)
 For s = 1 To UBound(arr, 2)
 tab2.Cells(i, j).Value = arr(z, s)
 i = i + 1
 Next s
 j = j + 1
Next z
End Sub

[drambeldier]

Hi Mama Leone,

Was muss geändert werden, damit die Daten in der Zieltabelle
an anderer Stelle geschrieben werden als in der Vorlage?

offset\_s = 3 ' horizontaler Versatz
offset\_z = 5 ' vertikaler Versatz

For z = 1 To UBound(arr, 1)
 For s = 1 To UBound(arr, 2)
 tab2.Cells(i + offset\_z, j + offset\_s).Value = arr(z, s)
 i = i + 1
 Next s
 j = j + 1
Next z

Gruß Ralf

[Mama_Leone]

Hallo Ralf,

das funktioniert nicht. Wenn ich z.B. einen Bereich mit 4 Spalten und 5 Zeilen übertragen will, kommen die Daten in der Zieltabelle versetzt an und nicht in einem Block, d.h. was vorher in einer Zeile war ist jetzt spaltenmässig angeordnet, dann springt er paar Zeilen nach unten und wiederholt dasselbe bei der zweiten Zeilen usw. Am Ende sieht das ganze wie eine Treppe aus.

Gruss

[Thomas_Ramel]

Grüezi Mama Leone

Der Code überträgt die Daten in die
Zieltabelle immer an die selbe Stelle wie er auch in der
Vorlage steht. Es ändert auch nichts, wenn in der Zieltabelle
vor dem Schreiben eine Zelle über Select ausgewählt wird. Ich
denke, das liegt daran, dass in beiden Tabellen die selben
Variablen verwendet werden.

IMO nicht, nein, sonderen wohl eher daran, dass den Variablen die gleichen Werte zugewiesen werden wie beim Einlesen.

Aber warum willst Du dies so umständlich über zwei Schleifen tun?
Uns weshalb müssen die Werte überhaupt in ein Array eingelesen werden - Du könntest sie ja auch gleich direkt übertragen?

Anyway - hier ein Code, der die Daten ausliest (ab B8 solange in Spalte B Daten sind und bis inkl. Spalte F) und sie im Zielblatt ab B8 einfügt. Diese Adresse kannst Du in der lezten Code-Zeile noch an deine Wünsche anpassen:

Public Sub Kopieren()
Dim lngLastRow As Long
Dim arTemp As Variant
 With Worksheets("Vorlage")
 lngLastRow = .Range("B65536").End(xlUp).Row
 arTemp = .Range("B8:B" & lngLastRow).Resize(, 5)
 End With
 Worksheets("Test").Range("B8").Resize(UBound(arTemp), 5) = arTemp
End Sub

[Mama_Leone]

Hallo Thomas,
nicht schlecht, Dein Code, auch wenn ich ihn noch nicht im Detail verstehe.

IMO nicht, nein, sonderen wohl eher daran, dass den Variablen
die gleichen Werte zugewiesen werden wie beim Einlesen.

so kann man es auch sagen

Aber warum willst Du dies so umständlich über zwei Schleifen
tun?

Habe ich eine Alternative, wenn Zeilen und Spalten eingelesen werden müssen?

Uns weshalb müssen die Werte überhaupt in ein Array eingelesen
werden - Du könntest sie ja auch gleich direkt übertragen?

ja, dazu habe ich auch folgendes entwickelt:

Sub daten()
Dim tab1 As Worksheet
Dim tab2 As Worksheet
Dim y As Long
Dim i As Long
Dim z As Long
Set tab1 = ActiveWorkbook.Worksheets("Vorlage")
Set tab2 = ActiveWorkbook.Worksheets("Test")
z = 8
Do Until tab1.Cells(z, 6) = ""
 z = z + 1
Loop
i = 8
For i = 1 To z - 1
 If tab1.Cells(i, 6).Value = "ja" Then
 y = 8
 Do Until tab2.Cells(y, 4) = ""
 y = y + 1
 Loop
 tab2.Rows(y - 1).Copy
 tab2.Rows(y).Insert shift:=xlDown
 Application.CutCopyMode = Fals
 tab2.Cells(y, 4).Value = tab1.Cells(i, 2).Value
 tab2.Cells(y, 5).Value = tab1.Cells(i, 3).Value
 tab2.Cells(y, 6).Value = tab1.Cells(i, 4).Value
 tab2.Cells(y, 8).Value = tab1.Cells(i, 5).Value
End If
Next i
End Sub

Nur was ist bei richtig grossen Datenbeständen? Hier folgt bei jedem Schleifendurchlauf ein Zugriff auf beide Tabellen, ausserdem müssen in der Zieltabelle Zeilen eingefügt werden, weil unten noch was steht und nicht überschrieben werden darf. Bei einem Array kann ich in einem Rutsch die Daten einlesen und in einem Rutsch wieder auslesen. Dass müsste bei grossen Beständen deutlich schneller sein. Die Flexibilität beim Plazieren der Daten in der Zieltabelle geht aber ein wenig flöten, wir habe es jetzt hingekriegt,dass diese verschoben werden kann, aber nur blockweise. Bei der direkten Übertragung wie oben im Code kann ich dagegen bei jeder zu übertragenden Spalte beeinflussen, wo diese in der Zieltabelle erscheint. Ich weiss noch nicht was ich am Ende nehme, man kann jedenfalls nicht immer alles haben.

gruss

Gruss

[Mama_Leone]

Hallo Ralf,

es geht jetzt, es lag daran, dass Du im ersten Beispiel noch i = i + 1 und j = j + 1 in der Schleife hattest und ich das so übernommen habe. Deshalb die Verzerrung.

Gruss

[Thomas_Ramel]

Grüezi Mama Leone

IMO nicht, nein, sonderen wohl eher daran, dass den Variablen
die gleichen Werte zugewiesen werden wie beim Einlesen.

so kann man es auch sagen

Das kann man nicht einfach so sagen, das sind die Tatsachen.

Aber warum willst Du dies so umständlich über zwei Schleifen
tun?

Habe ich eine Alternative, wenn Zeilen und Spalten eingelesen
werden müssen?

Das siehst Du ja in meinem Code - er liest den ganzen Bereich in einem Schlag in die Variable ein. Diese ist ein Variant-Array und kann benauso behandelt werden wie jedes andere Array auch.

Uns weshalb müssen die Werte überhaupt in ein Array eingelesen
werden - Du könntest sie ja auch gleich direkt übertragen?

ja, dazu habe ich auch folgendes entwickelt:

[Code gesnippt]

Auch hier liest Du Zelle für Zelle einzeln aus und schreibst sie dann in die andere Tabelle - das benötigt am meisten Aufwand da jede Zelle einzeln angefasst wird.
Meine Zeilen oben tun das auf einen Schlag - wenn Du weisst wo der Zielort ist, dann benötigst Du nicht mal ein Array in das Du die Daten einliest.

Nur was ist bei richtig grossen Datenbeständen?

Möglichst nicht alles einzeln anfassen - wenn Du einen Berg Kies an einen anderen Ort bringen musst, dann nimmst Du auch nicht jeden einzeln sondern immer mehrere zusammen mit einer Schaufel oder gar Schukarre.
Genauso solltest/kannst Du auch bei Bereichen vorgehen - wie oben gezeigt.

Hier folgt bei
jedem Schleifendurchlauf ein Zugriff auf beide Tabellen,
ausserdem müssen in der Zieltabelle Zeilen eingefügt werden,
weil unten noch was steht und nicht überschrieben werden darf.

Dann ermittle wie gross der einzufügende Bereich ist und füge dann erst alle Zeilen ein die nötig sind und übertrage die Werte erst dann wenn genügend Platz da ist.

Bei einem Array kann ich in einem Rutsch die Daten einlesen
und in einem Rutsch wieder auslesen. Dass müsste bei grossen
Beständen deutlich schneller sein. Die Flexibilität beim
Plazieren der Daten in der Zieltabelle geht aber ein wenig
flöten,

Absolut nicht - Wenn Du das Array beim schreiben zerlegen willst/musst kannst Du das ohne weiteres tun indem Du geeignete chleifen verwendest.
Oder aber Du liest nur immer die Bereiche als Array ein, die Du zusammenhängend übertragen kannst.

Bei der direkten Übertragung
wie oben im Code kann ich dagegen bei jeder zu übertragenden
Spalte beeinflussen, wo diese in der Zieltabelle erscheint.

Du könntest ja auch Spalte um Spalte übertragen - dann brauchst Du auch keine Schleife sondern kannst das direkt tun.

Um dir aber konkret(er) zu sagen wie Du das anstellen kannst ist mir der Aufbau deiner Daten viel zu wenig bekannt.
Schildere diesen doch klar und umfassend und auch was du mit dem Kopieren erreichen willst, dann können wir dich gezielter
unterstützen.
Oder lade eine Demo-Mappe auf einen WebSpace hoch und nenne hier dann den Linkt (die genaue Beschreibung ist dennoch notwedig).

–
Mit freundlichen Grüssen

Thomas Ramel

• MVP für MS-Excel -

[drambeldier]

Moin, ml,

noch ein Vorschlag: Array als Ganzes übertragen, dabei die Formatierung mitnehmen. Mir sit nämlich beim Testen aufgefallen, dass sich die bisherigen Lösungen darum nicht kümmern. Also:

 │ A │ B │ C │ D │ 
───┼────┼────────────┼──────┼─────┤
 6 │ 1 │ 01.01.2007 │ 123 │ 100 │ 
 7 │ 2 │ 01.02.2007 │ 246 │ 99 │ 
 8 │ 3 │ 01.03.2007 │ 369 │ 98 │ 
 9 │ 4 │ 01.04.2007 │ 492 │ 97 │ 
10 │ 5 │ 01.05.2007 │ 615 │ 96 │ 
11 │ 6 │ 01.06.2007 │ 738 │ 95 │ 
12 │ 7 │ 01.07.2007 │ 861 │ 94 │ 
13 │ 8 │ 01.08.2007 │ 984 │ 93 │ 
14 │ 9 │ 01.09.2007 │ 1107 │ 92 │ 
15 │ 10 │ 01.10.2007 │ 1230 │ 91 │ 
16 │ 11 │ 01.11.2007 │ 1353 │ 90 │ 
17 │ 12 │ 01.12.2007 │ 1476 │ 89 │ 
18 │ 13 │ 01.01.2008 │ 1599 │ 88 │ 
───┴────┴────────────┴──────┴─────┘

wird mit dem Code

Sub crwf() ' copy range with format

 Dim z\_lo As Long ' z für Zeile, S für Spalte,
 Dim s\_lo As Long ' l für links, r für rechts,
 Dim s\_ru As Long ' o für oben, u für unten
 Dim z\_ru As Long
 Dim z\_offset As Long
 Dim s\_offset As Long
 ' Ecken des Quellbereichs festlegen
 ' (resp. ausrechnen)
 z\_lo = 4
 s\_lo = 1
 z\_ru = 16
 s\_ru = 4
 ' Bereich kopieren
 Sheets("Vorlage").Select
 Range(Cells(z\_lo, s\_lo), Cells(z\_ru, s\_ru)).Select
 Selection.Copy
 ' Versatz wählen
 z\_offset = 2
 s\_offset = 1
 ' Ecken des Zielbereichs berechnen
 z\_lo = z\_lo + z\_offset
 s\_lo = s\_lo + s\_offset
 z\_ru = z\_ru + z\_offset
 s\_ru = s\_ru + s\_offset
 ' Bereich kopieren - mit Formatierung!

 Sheets("Test").Select
 Range(Cells(z\_lo, s\_lo), Cells(z\_ru, s\_ru)).Select
 Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False \_
 , Transpose:=False
End Sub

einschließlich der Formatierung übertragen:

 │ B │ C │ D │ E │ 
───┼────┼────────────┼──────┼─────┤
 8 │ 1 │ 01.01.2007 │ 123 │ 100 │ 
 9 │ 2 │ 01.02.2007 │ 246 │ 99 │ 
10 │ 3 │ 01.03.2007 │ 369 │ 98 │ 
11 │ 4 │ 01.04.2007 │ 492 │ 97 │ 
12 │ 5 │ 01.05.2007 │ 615 │ 96 │ 
13 │ 6 │ 01.06.2007 │ 738 │ 95 │ 
14 │ 7 │ 01.07.2007 │ 861 │ 94 │ 
15 │ 8 │ 01.08.2007 │ 984 │ 93 │ 
16 │ 9 │ 01.09.2007 │ 1107 │ 92 │ 
17 │ 10 │ 01.10.2007 │ 1230 │ 91 │ 
18 │ 11 │ 01.11.2007 │ 1353 │ 90 │ 
───┴────┴────────────┴──────┴─────┘

Gruß Ralf

[drambeldier]

Korrektur
Hi ml,

manchmal macht man sich das Leben unnötig schwer. Die Ecken des Bereiches lassen sich klarer benennen:

Sub crwf() ' copy range with format

 Dim z\_o As Long ' z für Zeile, s für Spalte,
 Dim s\_l As Long ' l für links, r für rechts,
 Dim z\_u As Long ' o für oben, u für unten
 Dim s\_r As Long 
 Dim z\_offset As Long
 Dim s\_offset As Long
 ' Ecken des Quellbereichs festlegen
 ' (resp. ausrechnen)
 z\_o = 4
 s\_l = 1
 z\_u = 16
 s\_r = 4
 ' Bereich kopieren
 Sheets("Vorlage").Select
 Range(Cells(z\_o, s\_l), Cells(z\_u, s\_r)).Select
 Selection.Copy
 ' Versatz wählen
 z\_offset = 2
 s\_offset = 1
 ' Ecken des Zielbereichs berechnen
 z\_o = z\_o + z\_offset
 s\_l = s\_l + s\_offset
 z\_u = z\_u + z\_offset
 s\_r = s\_r + s\_offset
 ' Bereich einfügen - mit Formatierung!

 Sheets("Test").Select
 Range(Cells(z\_o, s\_l), Cells(z\_u, s\_r)).Select
 Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False \_
 , Transpose:=False
End Sub

Gruß Ralf

[Mama_Leone]

Um dir aber konkret(er) zu sagen wie Du das anstellen kannst
ist mir der Aufbau deiner Daten viel zu wenig bekannt.
Schildere diesen doch klar und umfassend und auch was du mit
dem Kopieren erreichen willst, dann können wir dich gezielter
unterstützen.

Hallo Thomas,
meine Frage war nicht für eine bestimmte Aufgabe gedacht. Da ich häufig Daten übertragen muss, suchte ich nach einem ausgereiften Code, der sich flexibel einsetzen lässt. Meine Tabellen haben meist den Aufbau, dass die Anzahl der Spalten fest ist, die Zeilen aber varieren, d.h. diese müssen vom Code ermittelt werden. Eine zusätzlich Schwierigkeit ist häufig, dass nicht bis zur letzten Zeile, wo sich Summenzeilen befinden, gelesen werden darf. Usedrange.Rows.Count kann hier nicht verwendet werden, sondern ich lege einen Begriff vor der Summenzeile als Anker, der dann mit Do Until angesteuert wird.

Hier nochmal der Dank an alle drei Poster, die geholfen haben. Und ein kleiner Softwaretipp. Ich weiss nicht, wie Ihr euere Makrosammlung verwaltet, aber bei mir waren die früher ungeordnet in Exceldateien mit dem Ergebnis, dass das gezielte Finden von Makros ab einer bestimmten Grösse nur schwierig möglich war. Das Tool TheGuide http://theguide.sourceforge.net/ ist wie der Windows-Explorer aufgebaut. Im linken Fenster kann eine Baumstruktur mit beliebig vielen Ordnern angelegt werden und im rechten Fenster können die Makros abgelegt werden, aber nicht als Datei, die erst angeklickt und geöffnet werden muss (wie beim Explorer), sondern der Inhalt ist sofort sichtbar, wenn links ein Ordner angeklickt wird. Schneller kommt man nicht an seine Makros. Das Tool ist so eine Art Notizprogramm, dass ich jedenfalls nicht mehr missen möchte und mittlerweile für alle kurzen Texte nutze, wo es sich nicht lohnt gleich eine Worddatei anzulegen, die man später sowieso nicht findet.

Gruss