英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论