在列VBA中查找日期值 [英] Find date value in a column VBA

问题描述

我的Excel工作表中的数据我想在A列中找到一个日期.这是日期格式:"yyyy/mm/dd hh:mm:ss".它始终找不到任何内容，但是我要搜索的日期在A列中.这是我的代码的一部分:

data on my Excel sheetI would like to finde a date in column A. This is the date formát: "yyyy/mm/dd hh:mm:ss". It always find nothing, but the date what I am searching for is in the column A. This is a snippet of my code:

Dim LastDay As Date
Dim strdate As String
Dim rCell As Range

strdate = Format(LastDay, "yyyy/mm/dd hh:mm:ss")

Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlValues _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If rCell Is Nothing Then
MsgBox ("nothing")
Else

编辑:每个注释的新代码.

New code per comments.

Sub Copy()
    Dim LastDayRow As Long
    Dim FirstDayRow As Long
    Dim LastDay As Date
    Dim FirstDay As Date
    Dim rcell As Range

    LastDayRow = Range("E" & Rows.Count).End(xlUp).Row
    Range("E" & LastDayRow).Copy Range("G1")
    FirstDayRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & FirstDayRow).Copy Range("G2")
    LastDay = Cells(LastDayRow, "E").Value
    FirstDay = Cells(FirstDayRow, "A").Value

    Set rcell = Cells.Find(What:=LastDay, After:=Range("A1"), LookIn:=xlFormulas _
                , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

    If rcell Is Nothing Then
        MsgBox ("nothing")
    End If

End Sub

推荐答案

问题出在日期格式上.此日期格式中的"/":yyyy/mm/dd hh:mm:ss混淆了vba运行.源文件中的以下代码行解决了该问题:

The problem was the formát of the date. The "/" in this date format: yyyy/mm/dd hh:mm:ss confused the vba run. These code lines in the source file solved the problem:

Sheet1.Range("A:A").Replace What:="/", Replacement:="."
Sheet1.Range("A:A").NumberFormat = "yyyy/mm/dd hh:mm:ss"
Sheet3.Range("E:E").Replace What:="/", Replacement:="."
Sheet3.Range("E:E").NumberFormat = "yyyy/mm/dd hh:mm:ss"

这篇关于在列VBA中查找日期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！