2 Attachment(s)
[solved] [VBA] set range find() -> finds wrong value
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
Attachment 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
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
