VBA，具有一定值的goto单元格（类型：日期） [英] VBA, goto cell with a certain value (type: date)

问题描述

我有如下代码：

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = Range("A1") 
    If Trim(FindString) <> "" Then
        With Sheets("Kalendarz").Range("A5:LY5")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

但它不适用于日期格式，任何建议？

but it doesn't work with date format, any suggestion?

更多细节：在A1单元格中，我将输入一个日期，在5.行我有一个2016年的每一天的列表。我想（运行宏）后到单元格的日期从单元格A1。

More details: In A1 cell I will enter a date, In 5. row I have a list of every day in 2016. I want to (after run macro) go to cell with date from cell A1.

推荐答案

使用查找函数在Excel VBA中查找日期是非常棘手的。该功能依赖于您的搜索日期的格式与Excel的默认设置相同。此外，您需要确保使用 CDate 将搜索字符串转换为 Find 函数中的日期。 Ozgrid有一篇很好的文章： http://www.ozgrid.com/VBA/find -dates.htm

Using the Find function to locate a date is notoriously tricky in Excel VBA. The function relies on your search date being formatted in the same way as Excel's default setting. In addition, you need to ensure that the search string is converted to a date within the Find function, using CDate. Ozgrid has a good article on it: http://www.ozgrid.com/VBA/find-dates.htm

我已经修改了下面的代码以适应这些要求，并添加了一个额外的 With Sheets ... 语句，以确保 FindString 使用目标表中的范围。

I have amended your code below to accommodate those requirements and added an extra With Sheets... statement to ensure the FindString uses the Range from your target sheet.

然而，由于用户调整日期格式的不可预测性，我更喜欢VBA循环，使用Excel2的日期数字表示形式的Value2，因此不能被轻易解决。当一个 Find 函数快得多的时候，Ozgrid文章更喜欢不使用VBA循环，但是我想这是一个个人偏好的问题，我觉得一个定制循环更可靠。

However, because of the unpredictability of users' adjusting date formats, I prefer a VBA loop, using Value2 which is Excel's numerical representation of the date, so cannot be fiddled with. The Ozgrid article prefers not to use VBA loops when a Find function is so much faster, but I guess it's a matter of personal preference and I feel a bespoke loop is more reliable.

您最想要的是哪一个。

/ code>方法：

The Find method:

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range

    With Sheets("Kalendarz")
        FindString = .Range("A1")
        If Trim(FindString) <> "" Then
            With .Range("A5:LY5")
                Set Rng = .Find(What:=CDate(FindString), _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If
    End With
End Sub

VBA循环方法：

Sub Find_First_VBA_Loop()
    Dim dateVal As Long
    Dim cell As Range

    With Sheets("Kalendarz")
        dateVal = .Range("A1").Value2
        For Each cell In .Range("A5:LY5").Cells
            If dateVal = cell.Value2 Then
                Application.Goto cell, True
                Exit For
            End If
        Next
    End With

End Sub

这篇关于VBA，具有一定值的goto单元格（类型：日期）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！