vba - 表格内特定范围的填充日期

Question

我有一个表格，我想在其中插入日期，如图所示。它将把日期复制到某个连续范围。程序必须找到范围，然后使用输入框插入日期。我使用了下面的代码。问题是它没有选择表内的范围。怎么解决这个问题。帮帮我

Sub FillFirstDay()
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim table As ListObject
Dim dat As Date

Set ws = Sheets("Raw Data")
dat = Application.InputBox(prompt:="Enter the received date of the current Month", Title:="Date", Default:=Format(Date, "dd/mm/yyyy"), Type:=2)

If dat = False Then
MsgBox "Enter a Date", , "Date"
Exit Sub
End If

With ws
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    firstRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
    Set rng = Range(.Range("C" & firstRow), .Range("C" & LastRow))
End With

If firstRow >= LastRow Then Exit Sub

With rng
    .Value = dat
    .NumberFormat = "m/d/yyyy"
    .NumberFormat = "[$-409]dd-mmm-yy;@"
End With
End Sub

Answer 1

这行是问题所在:

firstRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1

.End(xlUp) 代码捕获向上移动的表格底部。您必须执行两次才能向上移动到数据所在位置的底部。此修改后的行将解决您的问题:

firstrow = .Range("C" & .Rows.Count).End(xlUp).End(xlUp).Row + 1