PDA

Vollständige Version anzeigen : Range Zellen kopieren, wenn Zielzelle leer


AndyFunk
20.09.2011, 11:19
Hallo

Ich würde gerne per VBA eine variable Range von einem Worksheet ins andere Kopieren. Geht dabei nur um die Wert der Zellen. Bedingung ist aber, dass der Wert nur kopiert wird, wenn die Zielzelle leer ist.
Da es sich um 1200 Zeilen x 35 Spalten handelt, ist mir mit For Each glaube ich nur bedingt geholfen, weil es doch recht langsam sein wird? Sind mehrere "Ranges" mit je 100Zx35S

Zudem kommen immer wieder Verbundene Zellen vor (auf die ich leider Gottes nicht verzichten kann, da sonst das ganze Sheet total durcheinander gerät.

Bin über jede Hilfe dankbar, komm momentan auf keinen grünen Zweig, wie ich das am besten angeh. Momentan bekomm ich nur alle Werte kopiert. Somit überschreibts mir in der Zieldatei jede Zelle, was nich sein darf.

Grüße, Andy

Minifuzzy
20.09.2011, 11:40
Hallo Andy,

mit einem normalen Range.Paste oder PasteSpecial geht das nicht. Du wirs also um ein For...Each nicht herumkommen.

Gruß
Minifuzzy

AndyFunk
20.09.2011, 11:46
Hallo Minifuzzy

Danke für die schnelle Antwort. Heißt also ich muss wirklich jede Zelle in der For .. Each ansprechen, abfragen ob diese leer ist und dann den Wert kopieren bzw einfach weitergehn? Ui ui, das wird dann nen langeres Prozedere :/

Gruß, Andy

AndyFunk
20.09.2011, 15:50
Sub HoleDaten()
Dim StDateiname As String
Dim x As Long
Dim y As Long
Dim Zelle As Range
Dim Zeile As Range
Dim Spalte As Long


For y = 0 To 95
For x = 0 To 31
Set Zeile = ThisWorkbook.Sheets("Tabelle1").Range("E5")
If Zeile.Offset(y, x) = "" Then
Zeile.Offset(y, x) = Workbooks(StDateiname).Sheets("Tabelle2").Range("E5").Offset(y, x)
End If
Next x
Next y


MsgBox "Daten wurden erfolgreich übertragen"

End Sub



So, das is momentan der Code für den "ersten Block", davon kommen nochmal paar Blöcke und das braucht jetzt schon ne gefühte Ewigkeit. Bin über jeden Vorschlag, wie man das schneller gestalten kann sehr Dankbar. Ich selber weiß leider nicht wie, da ich noch recht neu auf dem Gebiet programmieren (kann man das als programmieren bezeichnen?!) bin..

Grüße, Andy

IngGi
20.09.2011, 16:09
Hallo Andy,

wenn du mit Arrays arbeitest, geht es wesentlich schneller:

<blockquote><div style='background-color: #F2F2FF; border: 1px solid #3300B2; padding: 0px 24px;'><pre style='font-size: 12px; padding: 0px 10px;'><span style="color: #0000FF">Sub</span> HoleDaten<span style='color: #B200CC;'>(</span><span style='color: #B200CC;'>)</span><br><br><span style="color: #0000FF">Dim</span> StDateiname <span style="color: #0000FF">As</span> <span style="color: #0000FF">String</span><br><span style="color: #0000FF">Dim</span> vQuelle <span style="color: #0000FF">As</span> <span style="color: #0000FF">Variant</span><br><span style="color: #0000FF">Dim</span> vZiel <span style="color: #0000FF">As</span> <span style="color: #0000FF">Variant</span><br><span style="color: #0000FF">Dim</span> loZeile <span style="color: #0000FF">As</span> <span style="color: #0000FF">Long</span><br><span style="color: #0000FF">Dim</span> loSpalte <span style="color: #0000FF">As</span> <span style="color: #0000FF">Long</span><br><br><br>StDateiname = <span style='color: #CC9900;'>"NameDerZieldatei.xls"</span><br>vQuelle = ThisWorkbook.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle1"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"E5:AJ100"</span><span style='color: #B200CC;'>)</span><br>vZiel = Workbooks<span style='color: #B200CC;'>(</span>StDateiname<span style='color: #B200CC;'>)</span>.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle2"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"E5:AJ100"</span><span style='color: #B200CC;'>)</span><br><br><span style="color: #0000FF">For</span> loZeile =<span style="color: #FF0000"> 1</span> <span style="color: #0000FF">To</span><span style="color: #FF0000"> 96</span><br> <span style="color: #0000FF">For</span> loSpalte =<span style="color: #FF0000"> 1</span> <span style="color: #0000FF">To</span><span style="color: #FF0000"> 32</span><br> <span style="color: #0000FF">If</span> vZiel<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span> = <span style='color: #CC9900;'>""</span> <span style="color: #0000FF">Then</span><br> vZiel<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span> = vQuelle<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span><br> <span style="color: #0000FF">End </span><span style="color: #0000FF">If</span><br> <span style="color: #0000FF">Next</span> <span style='color: #008000;'>'loSpalte<br></span><span style="color: #0000FF">Next</span> <span style='color: #008000;'>'loZeile<br></span><br>Workbooks<span style='color: #B200CC;'>(</span>StDateiname<span style='color: #B200CC;'>)</span>.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle2"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"E5:AJ100"</span><span style='color: #B200CC;'>)</span> = vZiel<br><br><span style="color: #0000FF">End </span><span style="color: #0000FF">Sub</span><br></pre><hr style='color: #3300B2; background: #3300B2; height: 1px;'><p style='font-size: 8px; font-family: Verdana; text-align: right;'>VBA/HTML - CodeConverter für Excelforen<br>AddIn für Excel XP+2003 - komplett in VBA geschrieben von IngGi<br>Anbindung an VBE-Kontextmenü geklaut ;-) bei: <a href='http://www.cpearson.com/excel/VbeMenus.aspx'>http://www.cpearson.com/excel/VbeMenus.aspx</a></p></div></blockquote>

Gruß Ingolf

AndyFunk
20.09.2011, 16:51
Hallo Ingolf
Das ist besser, Danke!
Hast Du vielleicht auch noch nen Tipp, wie ich das elegant für die anderen Blöcke löse oder heißts für jeden Block das komplette Macro zu übernehmen nur eben mit angepassten Bereichen (also dass ich Bereiche anpassen muss is klar, nur eben ob ich nochmal das komplette Macro kopieren muss oder obs ne elegantere Möglichkeit gibt, 2. Block beginnt bei E104 und endet bei AI 199. Den Rest kann ich dann selbst :) )

Grüße und nen schönen Feierabend, Andy

IngGi
20.09.2011, 17:16
Hallo Andy,

man kann das Makro auch allgemeingültig schreiben, so dass es für einen beliebigen Bereich genutzt werden kann. Kopiere folgendes Makro in ein benutzerdefiniertes Modul (also ein Modul, dass du zuvor im VBA-Editor über das Menü Einfügen | Modul erzeugt hast):

<blockquote><div style='background-color: #F2F2FF; border: 1px solid #3300B2; padding: 0px 24px;'><pre style='text-align: center; font-size: 13px;'><span style='color: #008000;'>'#######################################################<br>'# #<br>'# Code einfügen in: #<br>'# ein allgemeines (benutzerdefiniertes) Modul #<br>'# #<br>'#######################################################<br></span></pre><hr style='color: #3300B2; background: #3300B2; height: 1px;'><pre style='font-size: 12px; padding: 0px 10px;'><span style="color: #0000FF">Sub</span> HoleDaten<span style='color: #B200CC;'>(</span>rBereich <span style="color: #0000FF">As</span> Range<span style='color: #B200CC;'>)</span><br><br><span style="color: #0000FF">Dim</span> StDateiname <span style="color: #0000FF">As</span> <span style="color: #0000FF">String</span><br><span style="color: #0000FF">Dim</span> vQuelle <span style="color: #0000FF">As</span> <span style="color: #0000FF">Variant</span><br><span style="color: #0000FF">Dim</span> vZiel <span style="color: #0000FF">As</span> <span style="color: #0000FF">Variant</span><br><span style="color: #0000FF">Dim</span> loZeile <span style="color: #0000FF">As</span> <span style="color: #0000FF">Long</span><br><span style="color: #0000FF">Dim</span> loSpalte <span style="color: #0000FF">As</span> <span style="color: #0000FF">Long</span><br><br><br>StDateiname = <span style='color: #CC9900;'>"NameDerZieldatei.xls"</span><br>vQuelle = ThisWorkbook.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle1"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span>rBereich.Address<span style='color: #B200CC;'>)</span><br>vZiel = Workbooks<span style='color: #B200CC;'>(</span>StDateiname<span style='color: #B200CC;'>)</span>.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle2"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span>rBereich.Address<span style='color: #B200CC;'>)</span><br><br><span style="color: #0000FF">For</span> loZeile =<span style="color: #FF0000"> 1</span> <span style="color: #0000FF">To</span> <span style="color: #0000FF">UBound</span><span style='color: #B200CC;'>(</span>rBereich,<span style="color: #FF0000"> 1</span><span style='color: #B200CC;'>)</span><br> <span style="color: #0000FF">For</span> loSpalte =<span style="color: #FF0000"> 1</span> <span style="color: #0000FF">To</span> <span style="color: #0000FF">UBound</span><span style='color: #B200CC;'>(</span>rBereich,<span style="color: #FF0000"> 2</span><span style='color: #B200CC;'>)</span><br> <span style="color: #0000FF">If</span> vZiel<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span> = <span style='color: #CC9900;'>""</span> <span style="color: #0000FF">Then</span><br> vZiel<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span> = vQuelle<span style='color: #B200CC;'>(</span>loZeile, loSpalte<span style='color: #B200CC;'>)</span><br> <span style="color: #0000FF">End </span><span style="color: #0000FF">If</span><br> <span style="color: #0000FF">Next</span> <span style='color: #008000;'>'loSpalte<br></span><span style="color: #0000FF">Next</span> <span style='color: #008000;'>'loZeile<br></span><br>Workbooks<span style='color: #B200CC;'>(</span>StDateiname<span style='color: #B200CC;'>)</span>.Sheets<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"Tabelle2"</span><span style='color: #B200CC;'>)</span>.Range<span style='color: #B200CC;'>(</span>rBereich.Address<span style='color: #B200CC;'>)</span> = vZiel<br><br><span style="color: #0000FF">End </span><span style="color: #0000FF">Sub</span></pre><hr style='color: #3300B2; background: #3300B2; height: 1px;'><p style='font-size: 8px; font-family: Verdana; text-align: right;'>VBA/HTML - CodeConverter für Excelforen<br>AddIn für Excel XP+2003 - komplett in VBA geschrieben von IngGi<br>Anbindung an VBE-Kontextmenü geklaut ;-) bei: <a href='http://www.cpearson.com/excel/VbeMenus.aspx'>http://www.cpearson.com/excel/VbeMenus.aspx</a></p></div></blockquote>

Dieses Makro kannst du nun für jeden zu kopierenden Bereich auf folgende Weise aufrufen:

<blockquote><div style='background-color: #F2F2FF; border: 1px solid #3300B2; padding: 0px 24px;'><pre style='font-size: 12px; padding: 0px 10px;'><span style="color: #0000FF">Sub</span> Bereiche<span style='color: #B200CC;'>(</span><span style='color: #B200CC;'>)</span><br><br>HoleDaten Range<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"E5:AI100"</span><span style='color: #B200CC;'>)</span><br>HoleDaten Range<span style='color: #B200CC;'>(</span><span style='color: #CC9900;'>"E104:AI199"</span><span style='color: #B200CC;'>)</span><br><br><span style="color: #0000FF">End </span><span style="color: #0000FF">Sub</span></pre><hr style='color: #3300B2; background: #3300B2; height: 1px;'><p style='font-size: 8px; font-family: Verdana; text-align: right;'>VBA/HTML - CodeConverter für Excelforen<br>AddIn für Excel XP+2003 - komplett in VBA geschrieben von IngGi<br>Anbindung an VBE-Kontextmenü geklaut ;-) bei: <a href='http://www.cpearson.com/excel/VbeMenus.aspx'>http://www.cpearson.com/excel/VbeMenus.aspx</a></p></div></blockquote>

Gruß Ingolf

AndyFunk
21.09.2011, 08:43
Morgen Ingolf!

Hab das mal übernommen in nen Modul, die Pfade angepasst und so. Bekomme nun
"Fehler beim Kompilieren
Erwartet: Datenfeld"

Abgesehen von nem bisschen Code davor (überprüfen, ob das Worksheet bereits offen ist, gibts kein Unterschied zum Code unten)

Gruß, Andy

AndyFunk
21.09.2011, 09:56
Wenn ich auf
To Ubound(rBereich, x)
verzichte, dann klappts, in diesem Fall kann ich damit gut leben, da die Ranges alle die selbe Anzahl von Zeilen und Spalten haben. Über Aufklärung des ganzen wär ich dennoch dankbar. Will ja nicht dumm steben ;)

Grüße, Andy

IngGi
21.09.2011, 10:31
Hallo Andy,

UBound muss sich natürlich auf vQuelle oder vZiel beziehen und nicht auf rBereich. Mein Fehler. Ob du vQuelle oder vZiel nimmst ist egal, denn beide sind ja gleich groß.

Gruß Ingolf

AndyFunk
21.09.2011, 10:34
Nun versteh ich, Danke. Problem gelöst :)
Kann zu
Andy