Schleife bauen
23.12.2005 09:39:24
Ellen
kann man für folgenden Code eine Schleife bauen? Sonst muß ich das Macro ewig von Hand weiterschreiben.
Merkmale:
- in SHIPMENT ADMIN NAT wird immer eine Zeile nach unten gegangen, start bei A11 -> A12,A13,A14....
- Es wird immer Bereich Zeile 2 bis 24 kopiert und zwar nach Zeile 62 -> dann 122,182,242..... also immer 60 Zeilen weiter
- die Zeile in der eingetragen wird verschiebt sich auch immer um 60 Zeilen
- die Zelle in der im SHIMPENT ADMIN NAT gesucht wird verschiebt sich immer um eine nach unten: 11 -> 12,13,14.....
Hier der Code, den ich gerne als Schleife hätte:
Sub go()
' If 2 Boxes -> 2nd Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A11") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("62:62").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(63, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(11, 2).Value
Worksheets("Box Address Label").Cells(68, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(11, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 3 Boxes -> 3rd Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A12") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("122:122").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(123, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(12, 2).Value
Worksheets("Box Address Label").Cells(128, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(12, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 4 Boxes -> 4th Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A13") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("182:182").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(183, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(13, 2).Value
Worksheets("Box Address Label").Cells(188, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(13, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 5 Boxes -> 5th Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A14") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("242:242").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(243, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(14, 2).Value
Worksheets("Box Address Label").Cells(248, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(14, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 6 Boxes -> 6th Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A15") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("302:302").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(303, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(15, 2).Value
Worksheets("Box Address Label").Cells(308, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(15, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 7 Boxes -> 7th Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A16") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("362:362").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(363, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(16, 2).Value
Worksheets("Box Address Label").Cells(368, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(16, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
' If 8 Boxes -> 8th Box Address Label
Sheets("SHIPMENT ADMIN NAT").Select
If Not Range("A17") = "" Then
Sheets("Box Address Label").Select
Rows("2:24").Copy
Rows("422:422").Select
ActiveSheet.Paste
Worksheets("Box Address Label").Cells(423, 5).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(17, 2).Value
Worksheets("Box Address Label").Cells(428, 6).Value = Worksheets("SHIPMENT ADMIN NAT").Cells(17, 6).Value
Range("A1").Select
Application.CutCopyMode = False
End If
End Sub
Vielen Dank im Voraus.
Gruß,
Ellen