Excel VBA - cannot .find date string in range

Question

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

Are you able to post a sample file for us to work with?

brettdj
– brettdj

2015-03-25 05:08:25 +00:00
Commented Mar 25, 2015 at 5:08 — brettdj
– brettdj, Commented Mar 25, 2015 at 5:08

Community · Accepted Answer · 2018-05-08 21:48:18Z

2

Using FIND with dates is finnicky, see here

Your code worked on my tested when I changed

Set TargetColumnRange = dumpsheet.Range("G2:G" & TargetValue).Find(what:=SourceColumnValue, _
                                                           LookIn:=xlFormulas, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows)

to

Set TargetColumnRange = dumpsheet.Range("G2:G" & TargetValue).Find(what:=DATEVALUE(SourceColumnValue), _
                                                           LookIn:=xlFormulas, _
                                                           LookAt:=xlWhole, _
                                                           SearchOrder:=xlByRows)

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Mar 23, 2015 at 5:55

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

brettdj Over a year ago

Nope - just experienced :) Thanks for the quick close-out.

PootyToot Over a year ago

It does behave funny, I ran it for the second time and it skipped over the dates, the first time it worked. I have reset excel and restarted and will try again

PootyToot · Accepted Answer · 2015-03-25 23:04:57Z

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

Collectives™ on Stack Overflow

Excel VBA - cannot .find date string in range

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related