Excel VBA - 无法在范围内查找日期字符串 [英] Excel VBA - cannot .find date string in range

问题描述

我写了一些VBA脚本来搜索列表中的日期范围，它可以找到日期，但由于某种原因，它们无法将它们与目标范围相匹配。我用一个vlookup测试了目标范围，这个vlookup会返回一个匹配项，但是.find代码看起来似乎不一样。

例如，sourcecolumnvalue将在其范围内选择一个日期（例如01/02/2015）。 sourcecolumnvalue将反映这一点，但似乎无法在.find字符串中设置的目标范围内找到。

我的代码有问题吗？

  Sub Finddates （）
 Dim SourceColumnValue As String，sourcerow As String，targetrow As String 
 Dim M As Long，O As Long，TargetValue As Long，actualsourcerow As Long，actualtargetrow As Long，actualtargetcolumn As Long，sourcedateposition As Long 
 TargetValue = dumpsheet.Cells（rows.Count，1）.End（xlUp）.row 
 sourcedateposition = dumpsheet.Cells（rows.Count，5）.End（xlUp）.row 
 
'循环源列
对于F = 1要提取的位置
 SourceColumnValue = dumpsheet.Cells（F，5）.Value 
 
 
'获取目标列匹配到
设置TargetColumnRange = dumpsheet.Range（G2：G& TargetValue）.Find（什么：= SourceColumnValue，_ 
 LookIn：= xlValues，_ 
 LookAt：= xlWhole ，_ 
 SearchO rder：= xlByRows）
'如果找到匹配
如果没有TargetColumnRange是没有
 TargetColumnRange.Value = SourceColumnValue 
 
对于O = 1转储表（A2：A& rows.Count）.End（xlUp）.row 
 Sourcename = ActiveCell（O，1）.Value 
 sourcerow = ActiveCell（O，2）.Value 
 targetrow = ActiveCell（O，3 ）.Value 
 
 actualsourcerow = CInt（sourcerow）
 actualtargetrow = CInt（targetrow）
 actualtargetcolumn = CInt（TargetColumn）
 
 CapexTargetSheet.Activate 
 Cells（actualtargetrow，actualtargetcolumn）.Value = CapexSourceSheet.Cells（actualsourcerow，F）.Value 
 Next O 
 End If 
 Next F 
 End Sub 
  

解决方案

我设法使用循环写一些代码，而不是使用.find碰巧与日期非常不一致。我读了另一篇文章，使用日期的字符串更好，因为日期的实际数值存储在字符串中。我将源和目标日期转换为字符串，然后使用循环进行匹配，该循环工作良好。但是谢谢你的回答，它确实让我走上正轨！

见下文

  Dim SourceColumnValue As String，sourcerow As String，targetrow As String，targetcolumnvalue As String，sourcecolumnnumber As String 
 Dim M As Long，O As Long，P As Long，TargetValue As Long，actualsourcerow As Long，actualtargetrow As Long，actualtargetcolumn As Long，sourcedateposition As Long，actualsourcecolumn As Long，targetdateposition As Long 
 Dim Copysource As Range，pastetarget As Range 
 
 TargetValue = dumpsheet.Cells（rows.Count ，1）.End（xlUp）.row 
 sourcedateposition = dumpsheet.Cells（rows.Count，5）.End（xlUp）.row 
 targetdateposition = dumpsheet.Cells（rows.Count，7）。结束（xlUp）.row 
 
'循环源列
对于F = 1要提交的位置
 SourceColumnValue = dumpsheet.Cells（F，5）.Value 
'Get目标列匹配到源
 
'循环比较字符串
对于P = 1 to targetdateposition 
 targetcolumnvalue = dumpsheet.Cells（P，7）.Value 
如果targetcolumnvalue = SourceColumnValue然后
 
 TargetColumnRange.Value = SourceColumnValue 
 targetcolumnvalue = dumpsheet.Cells （P，8）.Value 
 sourcecolumnnumber = dumpsheet.Cells（F，6）.Value 
 
 For O = 1 To dumpsheet.Cells（rows.Count，a）。End （xlUp）.row 
如果O> 1然后
 Sourcename = dumpsheet.Cells（O，1）.Value 
 sourcerow = dumpsheet.Cells（O，2）.Value 
 targetrow = dumpsheet.Cells（O，3）.Value 
 
'Set Integers 
 actualsourcerow = CInt（sourcerow）
 actualtargetrow = CInt（targetrow）
 actualtargetcolumn = CInt（targetcolumnvalue）
 actualsourcecolumn = CInt（sourcecolumnnumber ）
 
 
'复制并粘贴
设置Copysource = SourceSheet.Cells（actualsourcerow，actualsourcecolumn）
设置pastetarget = TargetSheet.Cells（actualtargetrow，actualtargetcolumn）
 Copysource.Copy 
 pastetarget.PasteSpecial（xlPasteValues）
 End If 
 Next O 
结束如果
下一个P 
下一个F 
  

I wrote some VBA script to search a range of dates in a list, it can find the dates, but for some reason it cannot match them to the target range. I tested the target range with a vlookup which does return a match, but the .find code doesn't seem to work in the same way.

For example, The sourcecolumnvalue will pick up a date in its range (say 01/02/2015). The sourcecolumnvalue will reflect this but cannot seem to find this in the target range as set within the .find string.

Am I doing something wrong with this code?

Sub Finddates()
Dim SourceColumnValue As String, sourcerow As String, targetrow As String
Dim M As Long, O As Long, TargetValue As Long, actualsourcerow As Long, actualtargetrow As Long, actualtargetcolumn As Long, sourcedateposition As Long
TargetValue = dumpsheet.Cells(rows.Count, 1).End(xlUp).row
sourcedateposition = dumpsheet.Cells(rows.Count, 5).End(xlUp).row

'Loop Source Column
For F = 1 To sourcedateposition
SourceColumnValue = dumpsheet.Cells(F, 5).Value


   'Get Target Column Match to Source
   Set TargetColumnRange = dumpsheet.Range("G2:G" & TargetValue).Find(What:=SourceColumnValue, _
                                                           LookIn:=xlValues, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows)
               'if a match is found
               If Not TargetColumnRange Is Nothing Then
                  TargetColumnRange.Value = SourceColumnValue

                       For O = 1 To dumpsheet.Range("A2:A" & rows.Count).End(xlUp).row
                         Sourcename = ActiveCell(O, 1).Value
                         sourcerow = ActiveCell(O, 2).Value
                         targetrow = ActiveCell(O, 3).Value

                         actualsourcerow = CInt(sourcerow)
                         actualtargetrow = CInt(targetrow)
                         actualtargetcolumn = CInt(TargetColumn)

                         CapexTargetSheet.Activate
                         Cells(actualtargetrow, actualtargetcolumn).Value = CapexSourceSheet.Cells(actualsourcerow, F).Value
                    Next O
               End If
Next F
End Sub

解决方案

I managed to write some code using a loop rather than using the .find which happened to be very inconsistent with dates. I read in another article that using strings for dates is better because the actual numerical value of the date gets stored in the string. I converted the source and target dates to strings and then did a match using a loop which works well. But thank you for your answer, it did put me on the right track!

See below

Dim SourceColumnValue As String, sourcerow As String, targetrow As String, targetcolumnvalue As String, sourcecolumnnumber As String
Dim M As Long, O As Long, P As Long, TargetValue As Long, actualsourcerow As Long, actualtargetrow As Long, actualtargetcolumn As Long, sourcedateposition As Long, actualsourcecolumn As Long, targetdateposition As Long
Dim Copysource As Range, pastetarget As Range

TargetValue = dumpsheet.Cells(rows.Count, 1).End(xlUp).row
sourcedateposition = dumpsheet.Cells(rows.Count, 5).End(xlUp).row
targetdateposition = dumpsheet.Cells(rows.Count, 7).End(xlUp).row

'Loop Source Column
For F = 1 To sourcedateposition
SourceColumnValue = dumpsheet.Cells(F, 5).Value
       'Get Target Column Match to Source

                ' Loop to compare strings
                    For P = 1 To targetdateposition
                    targetcolumnvalue = dumpsheet.Cells(P, 7).Value
                    If targetcolumnvalue = SourceColumnValue Then

                       TargetColumnRange.Value = SourceColumnValue
                       targetcolumnvalue = dumpsheet.Cells(P, 8).Value
                       sourcecolumnnumber = dumpsheet.Cells(F, 6).Value

                       For O = 1 To dumpsheet.Cells(rows.Count, "a").End(xlUp).row
                           If O > 1 Then
                           Sourcename = dumpsheet.Cells(O, 1).Value
                           sourcerow = dumpsheet.Cells(O, 2).Value
                           targetrow = dumpsheet.Cells(O, 3).Value

                           'Set Integers
                           actualsourcerow = CInt(sourcerow)
                           actualtargetrow = CInt(targetrow)
                           actualtargetcolumn = CInt(targetcolumnvalue)
                           actualsourcecolumn = CInt(sourcecolumnnumber)


                           'Copy and Paste
                           Set Copysource = SourceSheet.Cells(actualsourcerow, actualsourcecolumn)
                           Set pastetarget = TargetSheet.Cells(actualtargetrow, actualtargetcolumn)
                           Copysource.Copy
                           pastetarget.PasteSpecial (xlPasteValues)
                          End If
                      Next O
                   End If
                Next P
Next F

这篇关于Excel VBA - 无法在范围内查找日期字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！