PDA

View Full Version : [VBA] set range find() -> finds wrong value



dunnobe
01-24-2018, 06:51 AM
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

21444

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
01-24-2018, 08:36 AM
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
01-24-2018, 08:45 AM
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
01-24-2018, 08:49 AM
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
01-24-2018, 09:05 AM
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.