因此,我在Excel中创建了这个宏(DeleteOldData),它可以加快文件中多个数据表的数据编辑。该宏的目的是简单地根据单元格'D3'中的指示日期删除旧数据。当我第一次在excel文件中创建宏并运行它时,这段代码可以工作。我尝试将宏保存为.xlam(加载项)文件,以便其他工作表也可以使用此宏并保存一些时间。但是,当我将此宏添加到其他工作表时,代码会运行,但会将指定的日期读取为默认的12:00 AM。在调试时,当指示的日期为4/25/2023时,打印了以下内容。
输入的开始日期:12:00:00 AM
对于创建宏的原始excel工作表,它可以正常工作,但对于其他工作表则不能正常工作。我怀疑这和文件类型有关。我从工作簿位置保存它--而不是VBA编辑器窗口,因为它没有.xlam选项。是什么原因导致了这个问题?我该如何解决这个问题,以便我可以在其他工作表中使用这个加载项?这是我使用的代码:
Sub DeleteOldData()
Dim wb As Workbook
Dim ws As Worksheet
Dim startDate As Date
Dim lastRow As Long
Dim rng As Range
Dim firstStartDate As Date
Dim updatedWorksheets As String
' Set the workbook object to the current workbook
Set wb = ThisWorkbook
' Get the start date from the first worksheet
firstStartDate = wb.Worksheets(1).Range("D3").Value
' Loop through all the worksheets in the workbook
For Each ws In wb.Worksheets
startDate = firstStartDate
Debug.Print "Start date entered: " & startDate
' Find the first row with a date greater than or equal to the start date
Set rng = ws.Columns("A:A").Find(What:=startDate, LookAt:=xlPart, MatchCase:=True)
' If the start date is not found, exit the sub
If rng Is Nothing Then
MsgBox "Start date not found in Timestamp column"
Exit Sub
End If
' Find the last row of data
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Delete rows from the first row up to but not including the start date row
If rng.Row > 2 And rng.Row <= lastRow Then 'skip the header row and make sure it's not the last row
ws.Range("A2:C" & rng.Row - 1).Delete Shift:=xlUp 'Delete only columns A-C and shift remaining cells up
End If
' Shift the remaining data up
ws.Range("A2:C" & lastRow - rng.Row + 1).Cut Destination:=ws.Range("A2")
' Autofit the columns to adjust for the new data
ws.Columns("A:C").AutoFit
' Add the name of the updated worksheet to the list
updatedWorksheets = updatedWorksheets & ws.Name & ", "
Next ws
' Display a message box listing the updated worksheets
If Len(updatedWorksheets) > 0 Then
updatedWorksheets = Left(updatedWorksheets, Len(updatedWorksheets) - 2) ' Remove the last comma and space
MsgBox "Data updated successfully on the following worksheets: " & updatedWorksheets & "!"
Else
MsgBox "No worksheets were updated."
End If
End Sub
2条答案
按热度按时间pbwdgjma1#
Thisworkbook
是指包含正在运行的VBA代码的工作簿;即您的加载项。要引用“客户端”工作簿,您可能希望使用Application.ActiveWorkBook(或ActiveWorksheet)来引用其上的范围。xdnvmnnf2#
似乎引用的单元格值为0,而在“单元格格式”中,“数字”为“常规”。在本例中,单元格的Debug.Print以系统默认日期/时间格式给予0日期。添加到打印行
如果打印值为0,则为原因。在你的例子中应该是45041。
另一个想法:选择宏的模块,导出它,然后导入到数据所在的工作簿。这些命令(“导出”、“导入”)位于“文件”下。