VBA Excel - 按昨天的日期定义范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43381169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel - Define range by yesterdays date
提问by Loudo
Within VBA, I am trying to set the range as the day previous to when the workbook is open (e.g. Workbook is open on the 13/04/2017, I want the range to be 12/04/2017)
在 VBA 中，我试图将范围设置为工作簿打开的前一天（例如，工作簿在 13/04/2017 打开，我希望范围是 12/04/2017）
I would like VBA to search Column C for the previous day and when it finds it, it then selects the cells adjacent to it (A:I preferably).
我希望 VBA 搜索前一天的 C 列，当它找到它时，它然后选择与其相邻的单元格（A：我最好）。
Then, with this information, I would like the range to be set as to what was found with the search - This will then be converted to HTML and sent as an email.
然后，根据这些信息，我希望将范围设置为搜索找到的内容 - 然后将其转换为 HTML 并作为电子邮件发送。
Hopefully what I'm requesting makes sense.
希望我的要求是有道理的。
回答by primohacker
I'll get you 90% there. The code you want should look something like this. Based on your question I don't fully understand what vars you wanted set.
我会让你 90% 到那里。您想要的代码应该是这样的。根据您的问题，我不完全了解您想要设置的变量。
Dim d As Date
d = DateAdd("d", -1, Date)
Dim x As Range
Dim a As String
'Range("e1").Value = d
Columns("C:C").Select
a = Cells.Find(What:=d, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
'Range(a).Offset(0, 31).Range("A1:B1").Select
'adjust this
' x = Range(a).Offset(0, 31).Range("A1:B1").Address
回答by Shai Rado
Try the code below (explanation inside the code comments):
试试下面的代码（代码注释中的解释）：
Option Explicit
Sub FindYesterdaysDateRow()
Dim Rng As Range
Dim YesterD As Date
YesterD = DateAdd("d", -1, Date) ' <-- get yesterday's date
' the following line will work if you have column C formatted as "Date"
Set Rng = Range("C:C").Find(What:=YesterD, LookIn:=xlValues, LookAt:=xlWhole)
If Not Rng Is Nothing Then '<-- Find was successful
Range("A" & Rng.Row & ":I" & Rng.Row).Select
Else ' <-- Find was unable to find yesterday's date
MsgBox "Yesterday's date not found in Column C"
End If
End Sub
