[VBA] set range find() -> finds wrong value

[dunnobe]

Hey all,

I'm looking for some help with an annoying problem I ran into.

My goal is to transpose some data from a vertical to a horizontal structure.
A date is looked up in a datasheet and everytime it finds that date, it should copy a value next to it to my summary sheet. If it finds the date 2x, it should copy 2 cells etc.

The code can be run by clicking "Build data" and the code is saved in the module "BuildNewData" (see attachment).

Problem

The loop does not only find the date and copies its adjacent cells, but also find a similar data and copies its cells.

Example - what happens:

------------------------------------------------------------------------------------------
It looks up the value 15/01/2016 from ws Summary in wsTime -> sets it -> copies a value (c(, 2).Copy) -> next find date
It looks up the value 15/01/2016 from ws Summary in wsTime -> sets it -> copies a value (c(, 2).Copy) -> next find date

-> it should no longer find 15/01/2016 and go to next find date (16/01/2016)
-> but problem occurs

It looks up the value 15/01/2016 from ws Summary in wsTime -> sets 15/11/2016 -> copies a value (c(, 2).Copy) -> next find date
It looks up the value 15/01/2016 from ws Summary in wsTime -> sets 15/11/2016 -> copies a value (c(, 2).Copy) -> next find date
It looks up the value 15/01/2016 from ws Summary in wsTime -> sets 15/11/2016 -> copies a value (c(, 2).Copy) -> next find date
It looks up the value 15/01/2016 from ws Summary in wsTime -> sets15/11/2016 -> copies a value (c(, 2).Copy) -> next find date

->It no longer finds 15/01/2016 and goes to next find (16/01/2016)


It looks up the value 16/01/2016 from ws Summary in wsTime-> finds it...
------------------------------------------------------------------------------------------

Screenshot of the problem

bad.jpg

Ironically it works fine when looking up and copying 15/11/2016... the problem only occurs if the data starts in January (01) or February (02) AND when the sheet also has data for November (11) and December (12).
Is it the code, the way the days are formatted, ...?

Code

 Dim rng1 As Range, rng2 As Range, rng3 As Range, r As Range, c As Range, d As Range
    Dim ff As String, gg As String
    Set rng1 = wsSummary.Cells(1).CurrentRegion
    Set rng2 = wsTime.Cells(1).CurrentRegion.Columns("f")
    Set rng3 = wsPlanification.Cells(2).CurrentRegion.Columns("g")
    rng1.Offset(1, 4).ClearContents
    For Each r In rng1.Columns(1).Cells
        If IsDate(r.Value) Then
            Set d = rng3.Find(r.Value, , xlFormulas)
            If Not d Is Nothing Then
                    d(, 8).Copy r.Offset(, 2)
                    d(, 3).Copy r.Offset(, 3)
            Else
                r(, 3).Resize(, 2) = 0
            End If
            Set c = rng2.Find(r.Value, , xlFormulas)
            If Not c Is Nothing Then
                ff = c.Address
                Do
                    r(, 5) = r(, 5) + 1

                    Union(c(, 2), c(, 4), c(, 6), c(, 7), c(, 13)).Copy r.Offset(, r(, 5) * 5)
                    Set c = rng2.FindNext(c)
                Loop Until c.Address = ff
            Else
                r(, 5) = 0
            End If
        End If
    Next

A big thanks and respect to each and everyone who tries to help me!!!

dunnobe

[p45cal]

Change:
Set c = rng2.Find(r.Value, , xlFormulas)
to:
Set c = rng2.Find(r.Value, , xlFormulas, xlWhole)

and for good measure change:
Set d = rng3.Find(r.Value, , xlFormulas)
to:
Set d = rng3.Find(r.Value, , xlFormulas, xlWhole)

And if you're not on a Mac, you can tack on searchformat:=False to both lines because that's saved too:
Set c = rng2.Find(r.Value, , xlFormulas, xlWhole, searchformat:=False)

[From Help:
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used.]

[dunnobe]

Thanks p45cal,

I had just figured it out myself less than 1 hour ago.

lookat:= xlWhole makes it look for an exact match.

Can I give you +1 reputation somehow for your effort?

[p45cal]

If you must cross-post, as you have done, then it's a requirement to supply links here, to ALL places you have cross posted at.
The same applies at the one site where I've come across (one of?) your cross posts.
Why? Have a read of http://www.excelguru.ca/content.php?184
Ultimately it's to your benefit.

On top of that, once you have your solution, it's netiquette to update all places where you've asked the same question, telling them it's solved and how it was solved.

[dunnobe]

Thanks p45cal,

Sorry, but I thought I would reach a bigger audience by doing so.
I'm new to VBA and these forums, so I'm quite unaware of the community's opinion towards this.

I updated that topic too with the solution.