Excel VBA - 直到最后一行的日期 [英] Excel VBA - Date until last row

问题描述

我目前正在努力实现以下目标:

I am currently trying to achieve the following:

A 列中有数据(例如，Orange、Apple、Mango)，B 列为空.我想实现一个框，在输入日期时它会自动填充一个范围.

Column A has data in it (for example, Orange,Apple,Mango) and Column B is empty. I want to implement a box where upon entering a date it will autofill in a range.

这是我目前的代码:

Public Sub DATERES()
Dim strUserResponse As String

strUserResponse = InputBox("Enter Date")

ActiveSheet.Range("B1:B100").Value = strUserResponse

End Sub

到目前为止一切顺利，但是，我的问题是 A one 中的数据有时会在 1 到 500-600 之间变化.因此，我想修改它，使其首先检查 A 列，然后只在 B 列中输入日期直到最后一行，而不是给它更大的范围(如 B1:B1000).

So far so good, however, my problem is that the data in A one can vary from 1 to 500-600 at times. As such, I want to modify it so that it first checks column A, and only enters the date in column B until the last row, instead of giving it a much bigger range (like B1:B1000).

感谢任何帮助和建议.

祝您有美好的一天！

推荐答案

Public Sub DATERES()
Dim strUserResponse As String
dim lastrow as long

strUserResponse = InputBox("Enter Date")

lastrow = Cells(Rows.Count,1).end(xlup).row 'the 1 will get the last row for column A. Change if needed


ActiveSheet.Range("B1:B"& lastrow).Value = strUserResponse

End Sub

<小时>

使用引用的 Sheets 和 Range 而不是 ActiveSheet 更安全.

---

It's safer to use referenced Sheets and Ranges instead of ActiveSheet.

' modify "Sheet1" to your sheet's name
With Sheets("Sheet1")

    lastrow = .Cells(.Rows.count, 1).End(xlUp).Row ' the 1 will get the last row for column A. Change if needed

    .Range("B1:B" & lastrow).Value = strUserResponse
End With

这篇关于Excel VBA - 直到最后一行的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！