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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:09:53  来源:igfitidea点击:

Excel 2007 VBA Find row based on date

excel-vbaexcel-2007vbaexcel

提问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