Makro
Achim
Gruß Achim
https://www.herber.de/bbs/user/71081.xls
Sub KopierenNumPlanNachDispo()
Dim rNrn As Range
Dim lRow As Long
Dim lRo2 As Long
With Sheets("NMP Lang")
lRow = .Range("A65536").End(xlUp).Row
For Each rNrn In .Range("A3:A" & lRow)
lRo2 = 1 + Sheets("Disposition").Range("B65536").End(xlUp).Row
.Range("E2").Resize(1, .Range("E2").End(xlToRight).Column - 4).Copy
Sheets("Disposition").Range("F" & lRo2).PasteSpecial Transpose:=True, Paste:=xlValues
Sheets("Disposition").Range("B" & lRo2 & ":B" & lRo2 - 1 + .Range("E2").End(xlToRight). _
Column - 4).Value = rNrn.Value
Next 'rNrn
End With
With Sheets("Disposition")
.Range("A5:A" & lRo2).FormulaR1C1 = "=ROW()-4"
.Range("C5:C" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-1],'NMP Lang'!C[-2]:C[1],2,)"
.Range("D5:D" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-2],'NMP Lang'!C[-3]:C,3,)"
.Range("E5:E" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-3],'NMP Lang'!C[-4]:C[-1],4,)"
.Range("G5:G" & lRo2).FormulaR1C1 = "=TEXT(RC[-1],""TTT"")"
.Range("H5:H" & lRo2).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-6],'NMP Lang'!C1:C35,MATCH(RC[-2],'NMP Lang'!R2,),)="""","""",VLOOKUP( _
RC[-6],'NMP Lang'!C1:C35,MATCH(RC[-2],'NMP Lang'!R2,),))"
End With
End Sub
Sub KopierenNumPlanNachDispo()
Dim rNrn As Range
Dim lRow As LoadPictureConstants
Dim lRo2 As Long
With Sheets("NMP Lang")
lRow = .Range("A65536").End(xlUp).Row
For Each rNrn In .Range("A3:A" & lRow)
lRo2 = 1 + Sheets("Disposition").Range("B65536").End(xlUp).Row
.Range("E2").Resize(1, .Range("E2").End(xlToRight).Column - 4).Copy
Sheets("Disposition").Range("F" & lRo2).PasteSpecial Transpose:=True, Paste:=xlValues
Sheets("Disposition").Range("B" & lRo2 & ":B" & lRo2 - 1 + .Range("E2").End(xlToRight). _
Column - 4).Value = rNrn.Value
Next 'rNrn
End With
With Sheets("Disposition")
lRo2 = .Range("B65536").End(xlUp).Row
.Range("A5:A" & lRo2).FormulaR1C1 = "=ROW()-4"
.Range("C5:C" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-1],'NMP Lang'!C[-2]:C[1],2,)"
.Range("D5:D" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-2],'NMP Lang'!C[-3]:C,3,)"
.Range("E5:E" & lRo2).FormulaR1C1 = "=VLOOKUP(RC[-3],'NMP Lang'!C[-4]:C[-1],4,)"
.Range("G5:G" & lRo2).FormulaR1C1 = "=TEXT(RC[-1],""TTT"")"
.Range("H5:H" & lRo2).FormulaR1C1 = _
"=IF(VLOOKUP(RC[-6],'NMP Lang'!C1:C35,MATCH(RC[-2],'NMP Lang'!R2,),)="""","""",VLOOKUP( _
RC[-6],'NMP Lang'!C1:C35,MATCH(RC[-2],'NMP Lang'!R2,),))"
End With
End Sub