AW: If Schleife - unterschiedliche Ergebnisse F5/F8
05.02.2020 10:45:09
ChrisL
Hi
Gleiches Problem wie letztes mal...
With Worksheets("Versand").Range(Worksheets("Versand").Cells(32, 1), Worksheets("Versand").Cells(LastRow1, 10)).Borders
Hier mal ein (ungesteter) Versuch den Code etwas aufzuräumen:
Sub Test()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim Counter As Long
Dim lfdNr As Long
Application.ScreenUpdating = False
With Worksheets("Versand")
If .FilterMode Then .ShowAllData
LastRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
LastRow2 = Worksheets("Tabelle1").Cells(Rows.Count, 22).End(xlUp).Row
Do Until Trim(Worksheets("Tabelle1").Cells(LastRow2, 22).Text) ""
If LastRow2 = 1 Then Exit Do
LastRow2 = LastRow2 - 1
Loop
lfdNr = .Cells(LastRow1, 1).Value
For Counter = LastRow2 To LastRow2 - 40 Step -1
If Worksheets("Tabelle1").Cells(LastRow2, 22).Value = "" Then
LastRow2 = LastRow2 - 1
ElseIf Worksheets("Tabelle1").Cells(LastRow2, 24) = "" Then
.Cells(LastRow1 + 1, 1).EntireRow.Insert
.Range(.Cells(LastRow1 + 1, 2), .Cells(LastRow1 + 1, 3)).Merge
.Range(.Cells(LastRow1 + 1, 5), .Cells(LastRow1 + 1, 8)).Merge
.Cells(LastRow1 + 1, 1) = lfdNr + 1
.Cells(LastRow1 + 1, 2) = Worksheets("Tabelle1").Cells(LastRow2, 8).Value
.Cells(LastRow1 + 1, 4) = "aktiv"
.Cells(LastRow1 + 1, 5) = Worksheets("Tabelle1").Cells(LastRow2, 22).Value
.Cells(LastRow1 + 1, 9) = Worksheets("Tabelle1").Cells(LastRow2, 23).Value
.Cells(LastRow1 + 1, 10) = "100%"
.Cells(LastRow1 + 4, 10) = Date
lfdNr = lfdNr + 1
LastRow1 = LastRow1 + 1
Worksheets("Tabelle1").Cells(LastRow2, 24) = "übertragen"
LastRow2 = LastRow2 - 1
Else
Exit For
End If
Next Counter
With .Range(.Cells(32, 1), .Cells(LastRow1, 10)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.UsedRange.AutoFilter Field:=4, Criteria1:="aktiv"
End With
End Sub
cu
Chris