ich möchte per Makro eine Bereichsmarkierung mit einer For..Next Schleife um jeweils eine Zeile nach unten erweitern.
Bitte helft mir!!!
Bitte helft mir!!!
lngZeil=1
do
lngZeil=lngZeil+1
loop until cells(lngZeil,1)=""
for lngZeil=1 to 100
next
Bye
Nike
Sorry, aber ich "steh aufm Schlauch" :)
Bye
Nike
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
Bye
Nike
Z.B.
For i = 1 to 9
Zum schluss soll der Bereich dann so aussehen:
"C11:E19"
Das ist mein eigentliches Problem.
Ich möchte halt einfach eine Zeile nach der anderen "hinzumarkieren".
Ich hoffe du versteht was mein Problem ist.
Danek im vorraus