Hallo zusammen,

ich hoffe einer könnte mir behilflich sein, bin am verzweifeln.

Mein erster quellcode läuft einwandfrei, aber sobald ich eine 7. Variation dort abbilden möchte gibt es mir den Laufzeitfehler 9 an.


Sub FertigeVarriationsCSVerstellen()


Dim v1 As Integer, v2 As Integer, v3 As Integer, v4 As Integer, v5 As Integer, v6 As Integer 'Zeilen# für variantnvalues
Dim r As Long 'Zeilen# Ergebnis
Dim v As Integer 'Variation
Dim c As Long 'Spalten#
Dim ws As Worksheet 'Ziel-Tabelle
Dim sZeile As Long 'Startzeile

'Initialisierung
Set ws = Worksheets("Tabelle2")
sZeile = 4

'erste Ergebnsizeile
r = sZeile

With ws
.Range("A" & r) = Range("A2") & "-master"
.Range("B" & r) = Range("B2")
'.Range("C" & r) = Range("C2")
For c = 3 To 15 Step 2
.Cells(r, c) = Cells(2, c)
Next c

'alle übrigen Zeilen
For v1 = 2 To Range("q2").Value
For v2 = 2 To Range("r2").Value
For v3 = 2 To Range("s2").Value
For v4 = 2 To Range("t2").Value
For v5 = 2 To Range("u2").Value
For v6 = 2 To Range("v2").Value
r = r + 1
v = v + 1
.Range("A" & r) = Range("A2") & "-"
.Range("B" & sZeile & ":M" & sZeile).Copy Destination:=.Range("B" & r)
.Range("A" & r) = .Range("A" & r) & v
.Range("D" & r) = Range("D" & v1)
.Range("F" & r) = Range("F" & v2)
.Range("H" & r) = Range("H" & v3)
.Range("J" & r) = Range("J" & v4)
.Range("L" & r) = Range("L" & v5)
.Range("N" & r) = Range("N" & v6)
.Range("B" & r) = .Range("B" & r) & ", " & .Range("D" & r) & ", " & .Range("F" & r) _
& ", " & .Range("H" & r) & ", " & .Range("J" & r) & ", " & .Range("L" & r) _
& ", " & .Range("N" & r)
Next v6
Next v5
Next v4
Next v3
Next v2
Next v1
End With

Dim myArray
Dim lngLastRow As Long, i As Long, n As Long
With Sheets("Tabelle2")
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
myArray = .Range(.Cells(1, 1), .Cells(lngLastRow, 1))

'Array ohne feste größe damit es dynamisch ist
ReDim myArray(1 To UBound(myArray, 1))

'i ist ab welcher Zeile er anfangen soll die Werte aus den spalten auszulesen
For i = 5 To UBound(myArray)
If .Cells(i, 1) <> "" Then
n = n + 1
myArray(n) = .Cells(i, 1)
End If
Next
'bei myArray(1 to n) bedeutet die 1 erste Zeile und das n ist die zahl wieviel von der spalte A eingefügt werden soll
ReDim Preserve myArray(1 To n)
.Cells(2, 5) = Join(myArray, "|")
End With

Sheets("TabellenStrukturFürCSV").Select
Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FertigeCSV"
ActiveSheet.Paste
Sheets("Tabelle2").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
' ActiveWindow.SmallScroll Down:=3
Selection.Copy
Sheets("FertigeCSV").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Tabelle2").Select
' ActiveWindow.SmallScroll Down:=-105
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FertigeCSV").Select
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("C4").Select
Selection.Copy
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select

Range("T2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("D5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("U3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("V2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("Z2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("J5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AA3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AB2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("L5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AC3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AD2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("N5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AE3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub




Zweiter Code mit dem Laufzeitfehler 9

Sub FertigeVarriationsCSVerstellen()


Dim v1 As Long, v2 As Long, v3 As Long, v4 As Long, v5 As Long, v6 As Long, v7 As Long 'Zeilen# für variantnvalues
Dim r As Long 'Zeilen# Ergebnis
Dim v As Long 'Variation
Dim c As Long 'Spalten#
Dim ws As Worksheet 'Ziel-Tabelle
Dim sZeile As Long 'Startzeile

'Initialisierung
Set ws = Worksheets("Tabelle2")
sZeile = 4

'erste Ergebnsizeile
r = sZeile

With ws
.Range("A" & r) = Range("A2") & "-master"
.Range("B" & r) = Range("B2")

For c = 3 To 17 Step 2
.Cells(r, c) = Cells(2, c)
Next c

'alle übrigen Zeilen
For v1 = 2 To Range("q2").Value
For v2 = 2 To Range("r2").Value
For v3 = 2 To Range("s2").Value
For v4 = 2 To Range("t2").Value
For v5 = 2 To Range("u2").Value
For v6 = 2 To Range("v2").Value
For v7 = 2 To Range(w2).Value
r = r + 1
v = v + 1
.Range("A" & r) = Range("A2") & "-"
.Range("B" & sZeile & ":M" & sZeile).Copy Destination:=.Range("B" & r)
.Range("A" & r) = .Range("A" & r) & v
.Range("D" & r) = Range("D" & v1)
.Range("F" & r) = Range("F" & v2)
.Range("H" & r) = Range("H" & v3)
.Range("J" & r) = Range("J" & v4)
.Range("L" & r) = Range("L" & v5)
.Range("N" & r) = Range("N" & v6)
.Range("O" & r) = Range("O" & v7)
.Range("B" & r) = .Range("B" & r) & ", " & .Range("D" & r) & ", " & .Range("F" & r) _
& ", " & .Range("H" & r) & ", " & .Range("J" & r) & ", " & .Range("L" & r) _
& ", " & .Range("N" & r) & ", " & .Range("O" & r)
Next v7
Next v6
Next v5
Next v4
Next v3
Next v2
Next v1
End With

Dim myArray
Dim lngLastRow As Long, i As Long, n As Long
With Sheets("Tabelle2")
lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
myArray = .Range(.Cells(1, 1), .Cells(lngLastRow, 1))

'Array ohne feste größe damit es dynamisch ist
ReDim myArray(1 To UBound(myArray, 1))

'i ist ab welcher Zeile er anfangen soll die Werte aus den spalten auszulesen
For i = 5 To UBound(myArray)
If .Cells(i, 5) <> "" Then
n = n + 1
myArray(n) = .Cells(i, 5)
End If
Next
'bei myArray(1 to n) bedeutet die 1 erste Zeile und das n ist die zahl wieviel von der spalte A eingefügt werden soll



ReDim Preserve myArray(1 To n)'<--------------------- Hier kommt der Laufzeitfehler





.Cells(2, 5) = Join(myArray, "|")
End With
Sheets("TabellenStrukturFürCSV").Select
Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "FertigeCSV"
ActiveSheet.Paste
Sheets("Tabelle2").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets("FertigeCSV").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Tabelle2").Select

Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FertigeCSV").Select
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("C4").Select
Selection.Copy
Application.CutCopyMode = False
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select

Range("T2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("D5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("U3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("E2").Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("S2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("F5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("W3").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Tabelle2").Select

Range("G4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("X2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("H5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("Y3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("Z2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select

Range("J5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AA3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("K4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AB2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("L5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AC3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("M4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AD2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("N5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AE3").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Sheets("Tabelle2").Select
Range("P4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AF2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Tabelle2").Select
Range("P5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("FertigeCSV").Select
Range("AG3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub