Excel 2007 VBA 根据日期查找行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1981330/
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
Excel 2007 VBA Find row based on date
提问by MJ.
Date | data | data | data
12/29| G | F | G
12/30| G | |
I have a spreadsheet like above. I want to find the row that is the current date, then reference the row that is the current date in a Range type. Then cycle through the data in that row.
我有一个像上面这样的电子表格。我想找到当前日期的行，然后在 Range 类型中引用当前日期的行。然后循环遍历该行中的数据。
I can find the current date, and get the address of the cell that is the current date:
我可以找到当前日期，并获取当前日期所在单元格的地址：
dateRange = "A1:" & regionSheet.Range("A1").End(xlDown).Address
For Each cell In regionSheet.Range(dateRange)
If cell.Value = Date Then
row = cell.Address
End If
Next cell
That returns $A$2. I need to somehow turn this into a Rangetype. I tried using the cell.Address like below:
这将返回 2 澳元。我需要以某种方式把它变成一种Range类型。我尝试使用 cell.Address 如下所示：
row = cell.Address & ":" & regionSheet.Range(row).End(xlRight).Address
row = cell.Address & ":" & regionSheet.Range(row).End(xlRight).Address
but that errors out.
但那错误了。
Maybe I'm going about this the wrong way? Any ideas? Thanks!
也许我会以错误的方式解决这个问题？有任何想法吗？谢谢！
回答by shahkalpesh
range(cell, cell.End(xlToRight)).Address
OR
或者
range(cell.Address, range(cell.Address).End(xlToRight)).Address
EDIT: If you want it to have it in Rangetype, you could use
range(cell, cell.End(xlToRight))
编辑：如果你希望它有Range类型，你可以使用
range(cell, cell.End(xlToRight))
回答by dendarii
Be warned that the End() function can return incorrect results if there are gaps in the data. For example, if you had data in the second and fourth columns, End will not give you the result you want.
请注意，如果数据中存在间隙，End() 函数可能会返回不正确的结果。例如，如果您在第二列和第四列中有数据，End 不会给您想要的结果。
You could try something like this (assumes your data starts in row 1 and column 1):
您可以尝试这样的操作（假设您的数据从第 1 行和第 1 列开始）：
Sub RowOfCurrentDate()
Dim lngCurrDateRow As Long
Dim lngNumCols As Long
Dim rngDates As Range
Dim rngToday As Range
Dim c As Range
'Get current region and count the number of columns
Set rngDates = Range("A1").CurrentRegion
lngNumCols = rngDates.Columns.Count
'Resize the range down to one column
Set rngDates = rngDates.Resize(rngDates.Rows.Count, 1)
'Find today's date in the range
lngCurrDateRow = Application.WorksheetFunction.Match(CLng(Date), rngDates, 0)
'Set the range to search through for today
Set rngToday = Range(Cells(lngCurrDateRow, 1), Cells(lngCurrDateRow, lngNumCols))
'then loop through all cells in that range
For Each c In rngToday
'if cell is not empty
If Len(c) > 0 Then
'do something
End If
Next c
End Sub
