以下代码不能正常输出日期到指定单元格,请帮我修复,且告诉我错误的问题 Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range, cell As Range Dim arr() As Variant Dim cnt As Long Dim isCopying As Boolean ' 如果B1单元格为空,直接退出Sub过程 If Me.Range("B1").Value = "" Then Exit Sub If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Sheets("点位提取").Range("C5:C200").ClearContents If Me.Range("AH34").Value = True Then Me.ListBox1.AddItem "数据已被清空 " & Format(Now, "hh:mm:ss") Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 End If Set rng = Me.Range("B1:B2000") cnt = 0 isCopying = False For Each cell In rng If cell.Value = ":BEGIN" Then isCopying = True ReDim arr(2000) If Me.Range("AH34").Value = True Then Me.ListBox1.AddItem "开始提取数据 " & Format(Now, "hh:mm:ss") Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 End If ElseIf cell.Value = ":END" Then isCopying = False ReDim Preserve arr(cnt - 1) Sheets("点位提取").Range("C5").Resize(cnt, 1).Value = Application.Transpose(arr) If Me.Range("AH34").Value = True Then Me.ListBox1.AddItem "数据已进行提取完毕 " & Format(Now, "hh:mm:ss") Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 End If Exit For End If If isCopying And cell.Value <> ":BEGIN" Then arr(cnt) = rng.Cells(cell.Row, 1).Value cnt = cnt + 1 End If Next cell End If If Target.Address = "B2" Then Dim startDate As Date Dim endDate As Date startDate = DateSerial(Year(Date), Month(Date), Day(Date) - 3) endDate = Date Worksheets("数据配置").Range("E11").Value = Format(startDate, "yyyy-mm-dd") Worksheets("数据配置").Range("E12").Value = Format(startDate + 1, "yyyy-mm-dd") Worksheets("数据配置").Range("E13").Value = Format(startDate + 2, "yyyy-mm-dd") Worksheets("数据配置").Range("E14").Value = Format(endDate, "yyyy-mm-dd") End If On Error GoTo ErrorHandler Exit Sub ErrorHandler: If Me.Range("AH36").Value = True Then Me.ListBox2.AddItem Err.Description & " " & Format(Now, "hh:mm:ss") Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1 End If End Sub
时间: 2023-06-19 08:08:21 浏览: 119
这段代码存在一些问题,可能导致日期无法正常输出到指定单元格。具体问题如下:
1. 在日期格式化时,使用了错误的格式,应该使用"yyyy-mm-dd",而不是"yyyy/mm/dd"。
```
Worksheets("数据配置").Range("E11").Value = Format(startDate, "yyyy-mm-dd")
Worksheets("数据配置").Range("E12").Value = Format(startDate + 1, "yyyy-mm-dd")
Worksheets("数据配置").Range("E13").Value = Format(startDate + 2, "yyyy-mm-dd")
Worksheets("数据配置").Range("E14").Value = Format(endDate, "yyyy-mm-dd")
```
2. 在循环中,使用了错误的索引,应该使用cnt而不是cell.Row。
```
arr(cnt) = rng.Cells(cnt, 1).Value
```
3. 在循环结束后,应该清空数组,以便下一次使用。
```
ReDim arr(0)
```
修复后的代码如下:
```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim arr() As Variant
Dim cnt As Long
Dim isCopying As Boolean
' 如果B1单元格为空,直接退出Sub过程
If Me.Range("B1").Value = "" Then Exit Sub
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then
Sheets("点位提取").Range("C5:C200").ClearContents
If Me.Range("AH34").Value = True Then
Me.ListBox1.AddItem "数据已被清空 " & Format(Now, "hh:mm:ss")
Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
End If
Set rng = Me.Range("B1:B2000")
cnt = 0
isCopying = False
For Each cell In rng
If cell.Value = ":BEGIN" Then
isCopying = True
ReDim arr(2000)
If Me.Range("AH34").Value = True Then
Me.ListBox1.AddItem "开始提取数据 " & Format(Now, "hh:mm:ss")
Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
End If
ElseIf cell.Value = ":END" Then
isCopying = False
ReDim Preserve arr(cnt - 1)
Sheets("点位提取").Range("C5").Resize(cnt, 1).Value = Application.Transpose(arr)
If Me.Range("AH34").Value = True Then
Me.ListBox1.AddItem "数据已进行提取完毕 " & Format(Now, "hh:mm:ss")
Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
End If
ReDim arr(0)
Exit For
End If
If isCopying And cell.Value <> ":BEGIN" Then
arr(cnt) = rng.Cells(cnt, 1).Value
cnt = cnt + 1
End If
Next cell
End If
If Target.Address = "B2" Then
Dim startDate As Date
Dim endDate As Date
startDate = DateSerial(Year(Date), Month(Date), Day(Date) - 3)
endDate = Date
Worksheets("数据配置").Range("E11").Value = Format(startDate, "yyyy-mm-dd")
Worksheets("数据配置").Range("E12").Value = Format(startDate + 1, "yyyy-mm-dd")
Worksheets("数据配置").Range("E13").Value = Format(startDate + 2, "yyyy-mm-dd")
Worksheets("数据配置").Range("E14").Value = Format(endDate, "yyyy-mm-dd")
End If
On Error GoTo ErrorHandler
Exit Sub
ErrorHandler:
If Me.Range("AH36").Value = True Then
Me.ListBox2.AddItem Err.Description & " " & Format(Now, "hh:mm:ss")
Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1
End If
End Sub
```