Das Problem ist gleich in der 4. Zeile.
Ich mcöhte mit Range().select einen Bereich markieren.
Da die Lieferantenanzahl sich ändern, muss sich auch die Markierung gemäß der Lieferanten ändern.
Ich wollte es halt so machen:Range("A10:E10").select
und dann halt jeweils die ANzahl der Lieferanten weitermarkieren.
dass es dann eigentlich so aussieht: ungefähr
bei 6 Lieferanten:
Range("A10:E15").Select
Ich hoffe du verstehst es. :))
For ioben = 1 To Lieferanten
Sheets("Übersicht").Select
Sucheninhalt = "Lieferant " & ioben
Range("C11:E16").Select
Selection.Find(What:=Sucheninhalt, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, -1).Select
Zelleninhalt = ActiveCell.Text
Sucheninhalt = "Lieferant " & ioben
Range("C31:E36").Select
Selection.Find(What:=Sucheninhalt, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, -1).Select
ZelleninhaltQuant = ActiveCell.Text
If Sheets("Übersicht").Range("C11").Text <> "" And Sheets _
("Übersicht").Range("C25").Offset(Lieferanten, 0).Text <> "" Then
Rows("35:35").Offset(ioben + (2 * Lieferanten), 0).Select
Selection.Insert Shift:=xlDown
Range("G35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = "2"
Range("H35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = "=(RC[-3]*RC[-1]+RC[-4])/3"
Range("D35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = Zelleninhalt
Range("E35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = ZelleninhaltQuant
Range("C35:H35").Offset(ioben + (2 * Lieferanten), 0).Select
Selection.NumberFormat = "0.00"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("C35").Offset(ioben + (2 * Lieferanten), 0).Select
ActiveCell.FormulaR1C1 = "Lieferant " & ioben
ElseIf Sheets("Übersicht").Range("C11").Text = "" And Sheets("Übersicht").Range("C25").Text = "" Then
Rows("35:35").Offset(ioben, 0).Select
Selection.Insert Shift:=xlDown
Range("G35").Offset(ioben, 0).FormulaR1C1 = "2"
Range("H35").Offset(ioben, 0).FormulaR1C1 = "=(RC[-3]*RC[-1]+RC[-4])/3"
Range("D35").Offset(ioben, 0).FormulaR1C1 = Zelleninhalt
Range("E35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = ZelleninhaltQuant
Range("C35:H35").Offset(ioben, 0).Select
Selection.NumberFormat = "0.00"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("C35").Offset(ioben, 0).Select
ActiveCell.FormulaR1C1 = "Lieferant " & ioben
ElseIf Sheets("Übersicht").Range("C11").Text <> "" And Sheets("Übersicht").Range("C25").Offset(Lieferanten, 0).Text = "" Then
Rows("35:35").Offset(ioben + Lieferanten, 0).Select
Selection.Insert Shift:=xlDown
Range("G35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = "2"
Range("H35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = "=(RC[-3]*RC[-1]+RC[-4])/3"
Range("D35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = Zelleninhalt
Range("E35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = ZelleninhaltQuant
Range("C35:H35").Offset(ioben + Lieferanten, 0).Select
Selection.NumberFormat = "0.00"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("C35").Offset(ioben + Lieferanten, 0).Select
ActiveCell.FormulaR1C1 = "Lieferant " & ioben
ElseIf Sheets("Übersicht").Range("C11").Text = "" And Sheets("Übersicht").Range("C25").Text <> "" Then
Rows("35:35").Offset(ioben + Lieferanten, 0).Select
Selection.Insert Shift:=xlDown
Range("G35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = "2"
Range("H35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = "=(RC[-3]*RC[-1]+RC[-4])/3"
Range("D35").Offset(ioben + Lieferanten, 0).FormulaR1C1 = Zelleninhalt
Range("E35").Offset(ioben + (2 * Lieferanten), 0).FormulaR1C1 = ZelleninhaltQuant
Range("C35:H35").Offset(ioben + Lieferanten, 0).Select
Selection.NumberFormat = "0.00"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("C35").Offset(ioben + Lieferanten, 0).Select
ActiveCell.FormulaR1C1 = "Lieferant " & ioben
End If
Next ioben