Hilfe bei Code -> Teil einer Zelle in andere
23.12.2005 14:01:57
Ellen
wenn ich Werte von einem Tabellenblatt in ein anderes übertragen möchte mache ich das folgendermaßen:
Sub test()
Dim zeile As Integer
Dim c As Range
For zeile = 10 To Worksheets("SHIPMENT ADMIN NAT").Range("A65536").End(xlUp).row
Set c = Worksheets("DownLoad").Range("a:a").Find(Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 1).Value)
If Not c Is Nothing Then
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 8).Value = Worksheets("DownLoad").Cells(c.row, 2).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 9).Value = Worksheets("DownLoad").Cells(c.row, 59).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 10).Value = Worksheets("DownLoad").Cells(c.row, 6).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 11).Value = Worksheets("DownLoad").Cells(c.row, 7).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 12).Value = Worksheets("DownLoad").Cells(c.row, 53).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 13).Value = Worksheets("DownLoad").Cells(c.row, 12).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 14).Value = Worksheets("DownLoad").Cells(c.row, 13).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 15).Value = Worksheets("DownLoad").Cells(c.row, 14).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 16).Value = Worksheets("DownLoad").Cells(c.row, 15).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 18).Value = Worksheets("DownLoad").Cells(c.row, 16).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 19).Value = Worksheets("DownLoad").Cells(c.row, 17).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 20).Value = Worksheets("DownLoad").Cells(c.row, 18).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 21).Value = Worksheets("DownLoad").Cells(c.row, 19).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 22).Value = Worksheets("DownLoad").Cells(c.row, 28).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 23).Value = Worksheets("DownLoad").Cells(c.row, 29).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 24).Value = Worksheets("DownLoad").Cells(c.row, 30).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 25).Value = Worksheets("DownLoad").Cells(c.row, 31).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 27).Value = Worksheets("DownLoad").Cells(c.row, 32).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 28).Value = Worksheets("DownLoad").Cells(c.row, 33).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 29).Value = Worksheets("DownLoad").Cells(c.row, 34).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 30).Value = Worksheets("DownLoad").Cells(c.row, 35).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 31).Value = Worksheets("DownLoad").Cells(c.row, 49).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 32).Value = Worksheets("DownLoad").Cells(c.row, 55).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 33).Value = Worksheets("DownLoad").Cells(c.row, 36).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 34).Value = Worksheets("DownLoad").Cells(c.row, 37).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 35).Value = Worksheets("DownLoad").Cells(c.row, 38).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 36).Value = Worksheets("DownLoad").Cells(c.row, 39).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 38).Value = Worksheets("DownLoad").Cells(c.row, 40).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 39).Value = Worksheets("DownLoad").Cells(c.row, 41).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 40).Value = Worksheets("DownLoad").Cells(c.row, 42).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 41).Value = Worksheets("DownLoad").Cells(c.row, 43).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 42).Value = Worksheets("DownLoad").Cells(c.row, 48).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 43).Value = Worksheets("DownLoad").Cells(c.row, 5).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 44).Value = Worksheets("DownLoad").Cells(c.row, 58).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 45).Value = Worksheets("DownLoad").Cells(c.row, 56).Value
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 46).Value = Worksheets("DownLoad").Cells(c.row, 57).Value
End If
Next zeile
End Sub
Nun möchte ich im gleichen Tabellenblatt in Spalte 47 die ersten 12 Zeichen der Spalte 43 stehen haben. Über eine Formel ist mir schon klar, dass es mit Teil(...;1;12) funktioniert, ich möchte es jedoch in der Form wie oben haben. Habe es folgendermaßen versucht:
Sub test2()
'hier steht eigentlich gleicher der gleiche Code wie oben, zusätzlich:
Worksheets("SHIPMENT ADMIN NAT").Cells(zeile, 47).Value = Worksheets("SHIPMENT ADMIN NAT").Mid(Cells(c.row, 43), 1, 12)
End Sub
Jedoch war mir gleich klar, dass das so einfach nicht geht. Könnt ihr mir bitte helfen, wie ich den Code ändern muß?
Vielen Dank.
Gruß,
Ellen