VBA通过对比日期查找Excel指定列数据并复制到另外sheet
时间: 2024-01-24 12:02:12 浏览: 41
可以使用VBA中的DateDiff函数来比较日期,并使用For循环遍历指定列中的数据,找到符合条件的数据后将其复制到另一个sheet中。
以下是一个示例代码:
```VBA
Sub findDataByDate()
Dim startDate As Date, endDate As Date
Dim srcSheet As Worksheet, destSheet As Worksheet
Dim srcData As Range, destData As Range
Dim rowCounter As Long, destCounter As Long
'设置起始日期和结束日期
startDate = #1/1/2020#
endDate = #12/31/2020#
'获取源数据sheet和目标sheet
Set srcSheet = ThisWorkbook.Sheets("Data")
Set destSheet = ThisWorkbook.Sheets("Result")
'获取源数据范围
Set srcData = srcSheet.Range("A1:B10") '假设数据在A列和B列中
'初始化目标数据范围
Set destData = destSheet.Range("A1:B1")
destCounter = 0
'遍历源数据,查找符合条件的数据并复制到目标sheet
For rowCounter = 1 To srcData.Rows.Count
'比较日期是否在指定范围内
If DateDiff("d", startDate, srcData(rowCounter, 1).Value) >= 0 And DateDiff("d", endDate, srcData(rowCounter, 1).Value) <= 0 Then
'将符合条件的数据复制到目标sheet中
destCounter = destCounter + 1
destData.Resize(destCounter, 2).Value = srcData(rowCounter, 1).Resize(1, 2).Value
End If
Next rowCounter
End Sub
```
需要根据实际情况修改代码中的数据范围和目标sheet名称。