|
楼主 |
发表于 2024-4-29 17:06
|
显示全部楼层
用chatgpt做了一个,但是只能搞定第一次排序。
Sub 一按项目号排序() ' 带项目号的放在前面,不带项目号的放在后面,但是没有排序
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim startRow As Long
Dim endRow As Long
Dim oneRow As Long
Dim twoRow As Long
Dim sortRange As Range
Dim cell As Range
Dim moveRow As Long
Dim j As Long
' 设置要操作的工作表
Set ws = ThisWorkbook.Sheets("周报")
' 获取最后一行的行号
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
startRow = 0
endRow = 0
oneRow = 0
twoRow = 0
moveRow = 0
' 遍历数据
For i = 2 To lastRow
If ws.Cells(i, 1).Value = "一" Then
oneRow = i
startRow = i + 1
ElseIf ws.Cells(i, 1).Value = "二" Then
twoRow = i
endRow = i - 1
End If
' 找到"一"和"二"之间的数据行后进行处理
If startRow > 0 And endRow > 0 Then
' 对"一"和"二"之间的数据行进行排序
Set sortRange = ws.Range(ws.Cells(startRow, 1), ws.Cells(endRow, 16))
sortRange.Sort key1:=ws.Range("A" & startRow), order1:=xlAscending, Header:=xlNo
' 将包含"-"的行移动到"一"下方,不包含"-"的行移动到最后一个包含"-"的行的下方
For Each cell In sortRange.Columns(2).Cells
If InStr(cell.Value, "-") > 0 Then
If moveRow = 0 Then
moveRow = oneRow + 1
End If
ws.Rows(cell.Row).Cut Destination:=ws.Rows(moveRow)
moveRow = moveRow + 1
End If
Next cell
moveRow = 0
For j = sortRange.Rows.Count To 1 Step -1
If InStr(ws.Cells(sortRange.Cells(j, 2).Row, 2).Value, "-") > 0 Then
moveRow = ws.Cells(sortRange.Cells(j, 2).Row, 2).Row + 1
Exit For
End If
Next j
For Each cell In sortRange.Columns(2).Cells
If InStr(cell.Value, "-") = 0 Then
ws.Rows(cell.Row).Cut Destination:=ws.Rows(moveRow)
moveRow = moveRow + 1
End If
Next cell
Exit For
End If
Next i
End Sub |
|