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.
到目前为止,这是我的代码:
Here's my code so far:
Public Sub DATERES()
Dim strUserResponse As String
strUserResponse = InputBox("Enter Date")
ActiveSheet.Range("B1:B100").Value = strUserResponse
End Sub
但是,到目前为止,我的问题是A中的数据有时可能从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 Range
s 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屋!