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

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

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

问题

这是要翻译的内容：

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

以下是两个版本：

版本1（有效的版本）：

  1. Set rngFnd = wsElog1.Range("A7:A37")
  3. Dim i As Integer
  4. i = 0
  5. For Each rngRow In rngInput.Rows
  6.     vntInput = rngRow.Cells(1, 1)  ' 列A中的日期
  8.     If IsDate(vntInput) = True Then  ' 确保Excel将该值视为日期
  10.         nYear = Year(vntInput)
  11.         nMonth = Month(vntInput)
  13.         ' 日期是否在目标月份/年份内？
  14.         If nYear = nTargetYear And nMonth = nTargetMonth Then
  15.             SrchDt = Format(rngRow.Value, "d-mmm-yy")
  17.             Set rngFnd = wsElog1.Range("A7:A37").Find(SrchDt, LookIn:=xlValues)
  19.             Set rngTarget = wsTEff.Range("B" & rngRow.Row)
  21.             wsElog1.Range("GW" & rngFnd.Row).Copy
  22.             rngTarget.Offset(0, 0).PasteSpecial xlPasteValues
  23.             ' 然后是其他列的复制和粘贴...
  25.             i = i + 1
  26.         End If
  27.     End If
  28. Next rngRow

版本2（不起作用的版本）：

  1. Dim c As Integer
  2. c = 0
  3. For Each rngRow In rngInput.Rows
  4.     vntInput = rngRow.Cells(1, 1)  ' 列A中的日期
  6.     If IsDate(vntInput) = True Then  ' 确保Excel将该值视为日期
  8.         nYear = Year(vntInput)
  9.         nMonth = Month(vntInput)
  11.         ' 日期是否在目标月份/年份内？
  12.         If nYear = nTargetYear And nMonth = nTargetMonth Then
  13.             SrchDt1 = Format(rngRow.Value, "d/mm/yyyy")
  15.             Set rngFndA = wsRlog.Range("A:A").Find(What:=SrchDt1, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
  17.             Set RngTarget1 = wsTEff.Range("M" & rngRow.Row)
  19.             wsRlog.Range("CP" & rngFndA.Row).Copy
  20.             RngTarget1.Offset(0, 0).PasteSpecial xlPasteValues
  21.             ' 然后是其他列的复制和粘贴...
  23.             c = c + 1
  24.         End If
  25.     End If
  26. 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:

  1. Set rngFnd = wsElog1.Range("A7:A37")
  3. Dim i As Integer
  4. i = 0
  5.     For Each rngRow In rngInput.Rows
  6.         vntInput = rngRow.Cells(1, 1)  ' Dates in Column A
  8.         If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
  9.         
  10.             nYear = year(vntInput)
  11.             nMonth = Month(vntInput)
  12.         
  13.             ' Is the date within target month/year?
  14.             If nYear = nTargetYear And nMonth = nTargetMonth Then
  15.                SrchDt = Format(rngRow.Value, "d-mmm-yy")
  16.            
  17.                Set rngFnd = wsElog1.Range("A7:A37").Find(SrchDt, LookIn:=xlValues)
  18.            
  19.                            
  20.                     Set rngTarget = wsTEff.Range("B" & rngRow.Row)
  21.             
  22.                     wsElog1.Range("GW" & rngFnd.Row).Copy
  23.                     rngTarget.Offset(0, 0).PasteSpecial xlPasteValues
  24.                     wsElog1.Range("GX" & rngFnd.Row).Copy
  25.                     rngTarget.Offset(0, 1).PasteSpecial xlPasteValues
  26.                     wsElog1.Range("HL" & rngFnd.Row).Copy
  27.                     rngTarget.Offset(0, 2).PasteSpecial xlPasteValues
  28.                     wsElog1.Range("HH" & rngFnd.Row).Copy
  29.                     rngTarget.Offset(0, 3).PasteSpecial xlPasteValues
  30.                     wsElog1.Range("HK" & rngFnd.Row).Copy
  31.                     rngTarget.Offset(0, 4).PasteSpecial xlPasteValues
  32.                     wsElog1.Range("GZ" & rngFnd.Row).Copy
  33.                     rngTarget.Offset(0, 5).PasteSpecial xlPasteValues
  34.                     wsElog1.Range("HO" & rngFnd.Row).Copy
  35.                     rngTarget.Offset(0, 6).PasteSpecial xlPasteValues
  36.                     wsElog1.Range("HP" & rngFnd.Row).Copy
  37.                     rngTarget.Offset(0, 7).PasteSpecial xlPasteValues
  38.                 
  39.                          
  40.                 i = i + 1
  41.             End If
  42.         End If
  43.     Next rngRow

Version that does not work:

  1. Dim c As Integer
  2. c = 0
  3.     For Each rngRow In rngInput.Rows
  4.         vntInput = rngRow.Cells(1, 1)  ' Dates in Column A
  6.         If IsDate(vntInput) = True Then  ' Ensure Excel sees the value as a date
  7.         
  8.             nYear = year(vntInput)
  9.             nMonth = Month(vntInput)
  10.         
  11.             ' Is the date within target month/year?
  12.             If nYear = nTargetYear And nMonth = nTargetMonth Then
  13.                SrchDt1 = Format(rngRow.Value, "d/mm/yyyy")
  15.                Set rngFndA = wsRlog.Range("A:A").Find(What:=SrchDt1, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext, SearchFormat:=False)
  16.            
  17.                     Set RngTarget1 = wsTEff.Range("M" & rngRow.Row)
  18.                 
  19.                     wsRlog.Range("CP" & rngFndA.Row).Copy
  20.                     RngTarget1.Offset(0, 0).PasteSpecial xlPasteValues
  21.                     wsRlog.Range("CM" & rngFndA.Row).Copy
  22.                     RngTarget1.Offset(0, 1).PasteSpecial xlPasteValues
  23.                     wsRlog.Range("CX" & rngFndA.Row).Copy
  24.                     RngTarget1.Offset(0, 2).PasteSpecial xlPasteValues
  25.                     wsRlog.Range("CS" & rngFndA.Row).Copy
  26.                     RngTarget1.Offset(0, 3).PasteSpecial xlPasteValues
  27.                     wsRlog.Range("CU" & rngFndA.Row).Copy
  28.                     RngTarget1.Offset(0, 4).PasteSpecial xlPasteValues
  29.                     wsRlog.Range("CN" & rngFndA.Row).Copy
  30.                     RngTarget1.Offset(0, 5).PasteSpecial xlPasteValues
  31.                     wsRlog.Range("CL" & rngFndA.Row).Copy
  32.                     RngTarget1.Offset(0, 6).PasteSpecial xlPasteValues
  33.                     wsRlog.Range("CD" & rngFndA.Row).Copy
  34.                     RngTarget1.Offset(0, 7).PasteSpecial xlPasteValues
  36.                          
  37.                 c = c + 1
  38.             End If
  39.         End If
  40.     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

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

  1. Dim dt As Date, f As Range, rng As Range
  3. Set rng = ActiveSheet.Range("B3:B30")
  5. dt = Date + 3 '例如
  7. '使用与搜索范围中使用的日期格式相同的日期格式
  8. Set f = rng.Find(what:=Format(dt, rng.Cells(1).NumberFormat), _
  9.                  lookat:=xlWhole, LookIn:=xlValues)
  11. 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:

  1. Dim dt As Date, f As Range, rng As Range
  3. Set rng = ActiveSheet.Range("B3:B30")
  5. dt = Date + 3 'for example
  7. 'use the same date format as used in the search range
  8. Set f = rng.Find(what:=Format(dt, rng.Cells(1).NumberFormat), _
  9.                  lookat:=xlWhole, LookIn:=xlValues)
  11. Debug.Print dt, "Found?", Not f Is Nothing

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

发表评论

匿名网友

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

确定