搜索日期每次都返回空值(但日期以日期格式存在)

huangapple go评论65阅读模式
英文:

Search for date returns nothing every time (but date is there, in date format)

问题

这是要翻译的内容:

我有一些代码,对于日期格式为d-mmm-yy的一个工作表,它完美地工作,但对于日期格式为d/mm/yyyy的下一个工作表,同样的代码不起作用。我已经调整了代码以搜索新格式,但仍然什么都没有。 这里的代码在第一个工作表中搜索与月份和年份相关的日期,然后在另一个工作表中搜索该日期,以从该行复制单元格并将其带回原始工作表。

以下是两个版本:

版本1(有效的版本):

Set rngFnd = wsElog1.Range("A7:A37")

Dim i As Integer
i = 0
For Each rngRow In rngInput.Rows
    vntInput = rngRow.Cells(1, 1)  ' 列A中的日期

    If IsDate(vntInput) = True Then  ' 确保Excel将该值视为日期

        nYear = Year(vntInput)
        nMonth = Month(vntInput)

        ' 日期是否在目标月份/年份内?
        If nYear = nTargetYear And nMonth = nTargetMonth Then
            SrchDt = Format(rngRow.Value, "d-mmm-yy")

            Set rngFnd = wsElog1.Range("A7:A37").Find(SrchDt, LookIn:=xlValues)

            Set rngTarget = wsTEff.Range("B" & rngRow.Row)

            wsElog1.Range("GW" & rngFnd.Row).Copy
            rngTarget.Offset(0, 0).PasteSpecial xlPasteValues
            ' 然后是其他列的复制和粘贴...

            i = i + 1
        End If
    End If
Next rngRow

版本2(不起作用的版本):

Dim c As Integer
c = 0
For Each rngRow In rngInput.Rows
    vntInput = rngRow.Cells(1, 1)  ' 列A中的日期

    If IsDate(vntInput) = True Then  ' 确保Excel将该值视为日期

        nYear = Year(vntInput)
        nMonth = Month(vntInput)

        ' 日期是否在目标月份/年份内?
        If nYear = nTargetYear And nMonth = nTargetMonth Then
            SrchDt1 = Format(rngRow.Value, "d/mm/yyyy")

            Set rngFndA = wsRlog.Range("A:A").Find(What:=SrchDt1, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)

            Set RngTarget1 = wsTEff.Range("M" & rngRow.Row)

            wsRlog.Range("CP" & rngFndA.Row).Copy
            RngTarget1.Offset(0, 0).PasteSpecial xlPasteValues
            ' 然后是其他列的复制和粘贴...

            c = c + 1
        End If
    End If
Next rngRow

RngFndA 的范围查找总是返回空值,我无法弄清楚原因。

问题已解决:我需要添加 Dim SrchDt1 As Date

英文:

I have some code which works perfectly for one sheet with date format d-mmm-yy, but the same code will not work on the next sheet which has date format d/mm/yyyy. I have adjusted the code to search for the new format, but still get nothing.
The code here searches the first sheet for relevant dates based on month and year, then searches for that date in another sheet in order to copy cells from that row and bring them back to the original sheet.

Here are both versions:

Version that works:

Set rngFnd = wsElog1.Range("A7:A37")

Dim i As Integer
i = 0
    For Each rngRow In rngInput.Rows
        vntInput = rngRow.Cells(1, 1)  ' Dates in Column A

        If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
        
            nYear = year(vntInput)
            nMonth = Month(vntInput)
        
            ' Is the date within target month/year?
            If nYear = nTargetYear And nMonth = nTargetMonth Then
               SrchDt = Format(rngRow.Value, "d-mmm-yy")
           
               Set rngFnd = wsElog1.Range("A7:A37").Find(SrchDt, LookIn:=xlValues)
           
                           
                    Set rngTarget = wsTEff.Range("B" & rngRow.Row)
            
                    wsElog1.Range("GW" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 0).PasteSpecial xlPasteValues
                    wsElog1.Range("GX" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 1).PasteSpecial xlPasteValues
                    wsElog1.Range("HL" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 2).PasteSpecial xlPasteValues
                    wsElog1.Range("HH" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 3).PasteSpecial xlPasteValues
                    wsElog1.Range("HK" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 4).PasteSpecial xlPasteValues
                    wsElog1.Range("GZ" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 5).PasteSpecial xlPasteValues
                    wsElog1.Range("HO" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 6).PasteSpecial xlPasteValues
                    wsElog1.Range("HP" & rngFnd.Row).Copy
                    rngTarget.Offset(0, 7).PasteSpecial xlPasteValues
                
                         
                i = i + 1
            End If
        End If
    Next rngRow

Version that does not work:

Dim c As Integer
c = 0
    For Each rngRow In rngInput.Rows
        vntInput = rngRow.Cells(1, 1)  ' Dates in Column A

        If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
        
            nYear = year(vntInput)
            nMonth = Month(vntInput)
        
            ' Is the date within target month/year?
            If nYear = nTargetYear And nMonth = nTargetMonth Then
               SrchDt1 = Format(rngRow.Value, "d/mm/yyyy")

               Set rngFndA = wsRlog.Range("A:A").Find(What:=SrchDt1, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
           
                    Set RngTarget1 = wsTEff.Range("M" & rngRow.Row)
                
                    wsRlog.Range("CP" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 0).PasteSpecial xlPasteValues
                    wsRlog.Range("CM" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 1).PasteSpecial xlPasteValues
                    wsRlog.Range("CX" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 2).PasteSpecial xlPasteValues
                    wsRlog.Range("CS" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 3).PasteSpecial xlPasteValues
                    wsRlog.Range("CU" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 4).PasteSpecial xlPasteValues
                    wsRlog.Range("CN" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 5).PasteSpecial xlPasteValues
                    wsRlog.Range("CL" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 6).PasteSpecial xlPasteValues
                    wsRlog.Range("CD" & rngFndA.Row).Copy
                    RngTarget1.Offset(0, 7).PasteSpecial xlPasteValues

                         
                c = c + 1
            End If
        End If
    Next rngRow

The range find always returns nothing for RngFndA and I can't figure out why.

SOLVED: I needed to add Dim SrchDt1 As Date

答案1

得分: 1

如果要搜索的范围包含格式一致的实际日期,您可以像这样操作:

Dim dt As Date, f As Range, rng As Range

Set rng = ActiveSheet.Range("B3:B30")

dt = Date + 3 '例如

'使用与搜索范围中使用的日期格式相同的日期格式
Set f = rng.Find(what:=Format(dt, rng.Cells(1).NumberFormat), _
                 lookat:=xlWhole, LookIn:=xlValues)

Debug.Print dt, "找到?", Not f Is Nothing
英文:

If the range being searched contains actual dates which are consistently-formatted then you could do something like this:

Dim dt As Date, f As Range, rng As Range

Set rng = ActiveSheet.Range("B3:B30")

dt = Date + 3 'for example

'use the same date format as used in the search range
Set f = rng.Find(what:=Format(dt, rng.Cells(1).NumberFormat), _
                 lookat:=xlWhole, LookIn:=xlValues)

Debug.Print dt, "Found?", Not f Is Nothing

huangapple
  • 本文由 发表于 2023年6月1日 06:38:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76377697.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定