日记首页
|
列表
|
添加日记
|
管理登陆
标题：vba 日期排序
<div style=""><font face="Arial"><span style="font-size: 14px;">Private Sub CommandButton7_Click() </span></font><span style="font-family: Arial; font-size: 14px;"> </span><span style="font-family: Arial; font-size: 14px;">' 改按钮名字</span></div><div style=""><font face="Arial"><span style="font-size: 14px;"> Call 按日期排序</span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;"> Call 按日期排序2</span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;"> Call 边框线</span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;">End Sub</span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;"><br></span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;">*****************************************************************</span></font></div><div style=""><font face="Arial"><span style="font-size: 14px;">《</span></font><span style="font-family: Arial; font-size: 14px;">日期排序--</span><span style="font-size: 14px; font-family: Arial;">模块》</span></div><div style=""><font face="Arial"><div style=""><span style="font-size: 14px;">Sub 按日期排序()</span></div><div style=""><span style="font-size: 14px;"> Dim lastRow As Long</span></div><div style=""><span style="font-size: 14px;"> Dim ws As Worksheet</span></div><div style=""><span style="font-size: 14px;"> Set ws = ThisWorkbook.Worksheets("Sheet2") ' 修改为你的工作表名称</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> With ws</span></div><div style=""><span style="font-size: 14px;"> lastRow = .Cells(.Rows.count, "C").End(xlUp).Row ' 获取C列最后一行</span></div><div style=""><span style="font-size: 14px;"> Dim currentDate As Date</span></div><div style=""><span style="font-size: 14px;"> Dim count As Integer</span></div><div style=""><span style="font-size: 14px;"> count = 1 ' 设置计数器初始值为1</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> For i = 2 To lastRow ' 从第2行开始遍历</span></div><div style=""><span style="font-size: 14px;"> If IsDate(.Cells(i, "C").Value) Then ' 检查C列是否为日期</span></div><div style=""><span style="font-size: 14px;"> If .Cells(i, "C").Value = currentDate Then ' 检查是否为同一天</span></div><div style=""><span style="font-size: 14px;"> .Cells(i, "F").Value = count ' 写入计数器值到F列</span></div><div style=""><span style="font-size: 14px;"> count = count + 1 ' 计数器加1</span></div><div style=""><span style="font-size: 14px;"> Else</span></div><div style=""><span style="font-size: 14px;"> ' 标注前一个日期组的最后一行的颜色</span></div><div style=""><span style="font-size: 14px;"> If i > 2 Then</span></div><div style=""><span style="font-size: 14px;"> .Range("F" & i - 1).Interior.Color = RGB(255, 0, 0) ' 上一个日期组的颜色</span></div><div style=""><span style="font-size: 14px;"> End If</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> currentDate = .Cells(i, "C").Value ' 更新当前日期</span></div><div style=""><span style="font-size: 14px;"> count = 1 ' 重置计数器为1</span></div><div style=""><span style="font-size: 14px;"> .Cells(i, "F").Value = count ' 写入计数器值到F列</span></div><div style=""><span style="font-size: 14px;"> count = count + 1 ' 计数器加1</span></div><div style=""><span style="font-size: 14px;"> End If</span></div><div style=""><span style="font-size: 14px;"> End If</span></div><div style=""><span style="font-size: 14px;"> Next i</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> ' 标注最后一个日期组的最后一行的颜色</span></div><div style=""><span style="font-size: 14px;"> If lastRow > 1 Then</span></div><div style=""><span style="font-size: 14px;"> .Range("F" & lastRow).Interior.Color = RGB(255, 0, 0)</span></div><div style=""><span style="font-size: 14px;"> End If</span></div><div style=""><span style="font-size: 14px;"> End With</span></div><div style=""><span style="font-size: 14px;">End Sub</span></div><div style=""><span style="font-size: 14px;"><br></span></div><div style=""><span style="font-size: 14px;"><br></span></div><div style=""><span style="font-size: 14px;">Sub 按日期排序2()</span></div><div style=""><span style="font-size: 14px;"> Dim lastRow As Long</span></div><div style=""><span style="font-size: 14px;"> Dim ws As Worksheet</span></div><div style=""><span style="font-size: 14px;"> Set ws = ThisWorkbook.Worksheets("Sheet2") ' 修改为你的工作表名称</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> With ws</span></div><div style=""><span style="font-size: 14px;"> lastRow = .Cells(.Rows.count, "C").End(xlUp).Row ' 获取C列最后一行</span></div><div style=""><span style="font-size: 14px;"> </span></div><div style=""><span style="font-size: 14px;"> For i = 2 To lastRow ' 从第2行开始遍历</span></div><div style=""><span style="font-size: 14px;"> If Not IsDate(.Cells(i, "C").Value) Then ' 检查C列是否为日期</span></div><div style=""><span style="font-size: 14px;"> .Cells(i, "F").Interior.Color = RGB(255, 255, 255) ' 非日期数据标注白色</span></div><div style=""><span style="font-size: 14px;"> .Cells(i - 1, "F").Interior.Color = RGB(255, 0, 0) ' 非日期数据标注红色</span></div><div style=""><span style="font-size: 14px;"> End If</span></div><div style=""><span style="font-size: 14px;"> Next i</span></div><div style=""><span style="font-size: 14px;"> End With</span></div><div style=""><span style="font-size: 14px;">End Sub</span></div><div style="font-size: 14px;"><br></div></font></div>
