Comparing and Copying Values Between Dates

My VBA skills are quite lacking (hence my request), but the macro I think would be pretty easy to program. I've written out the approach below and as you'll see it's pretty doable (although not for the untrained like myself). I'd really appreciate any assistance with this please.

Essentially, I’m attempting to find values that are within a band of .001 of the values of cells in columns L through O and searching columns B through I for values that meet the .001 criteria and copying the values to columns Q, R, S, and T respectively.

I mentioned the values in L, M, N and O are where the comparisons will be made and these values are tagged to a specific date in Column K. I need to find the dates in Column A that are in between the values of K and the cell beneath it. For example, in K4 I have a date of 10/3/99 and the next date I have in K5 is 10/5/99. I would then query Column A for the dates 10/06/99 (A4), 10/07/99 (A5), and 10/08/99 (A6). The values I need to find within the .001 band are in Columns B through I (B4:I6). Lastly, when reach the last value in Column K, query all values in Column A greater than last value in K and copy the appropriate values in B through I to Q through T.

Rules for copying data to Column Q, R, S, and T:

For Column Q-
Find values in B, C, or D that’s within plus or minus .001 of M and copy the value of B, C, or D into Column Q of the same row.

For Column R-
Find values in B, C, D, or E that’s within plus or minus .001 of L and copy the value of B, C, D, or E into Column R of the same row.

For Column S-
Find values in G, H, or I that’s within plus or minus .001 of N and copy the value of G, H, or I into Column S of the same row.

For Column T-
Find values F, G, H, or I that’s within plus or minus .001 of O and copy the value of F, G, H, or I into Column T of the same row.

***I attached a sample and completed what the results would be for the first 3 values of K so you can see the logic. There should be only one value that’s copied into Q, R, S, or T. If the difference is exactly .001 the value should be copied.

Hope all is clear and if not feel free to ask any questions. Again, I appreciate any help on this!!

Originally Posted by StephenR

So are you saying the values in B4:I6 should be compared with L4:O4? I don't really understand why you have the values in Q5 and T5.

Thanks for checking this out Stephen.

The value for Q5 is the result of taking the value from M4 and comparing to B5. Is it within the .001 range, yes so that value is copied to Q4. So for Column Q, whenever there's a value in B, C, or D that's within the .001 range it's copied into Q.

After copying B5 to Q5, the macro would then compare M4 to C5. Is it within the .001 range, no so no value is copied. Then a comparison from M4 to D5. Is it within the .001 range, no so no value copied.

Then the macro would move down to B6 and compare it again to M4. Is it within .001 range, no so no val copied (if it was within the range the B6 value would be copied to Q6). Then M4 is compared to C6 and D6 to see if it's within the range and none of the values meet the criteria so no values are copied.

D6 would be the last value compared to M4. The next comparisons would be between M5 and B7:D11. M5 corresponds to the K5 date of 10/10/99 so we look at dates in Column A (A7:A11) 10/11/99-10/15/99.


Regarding T5 Values:
First O4 is compared to F5. Is it within .001 range, yes so F5 value copied to T5. So for Column T, whenever there's a value in F, G, H, or I that's within the .001 range it's copied to T.

Then O4 is compared to G5, is it within the .001 range, no so no value copied. O4 is then compared to H5 and I5 but none of those values are within the range.

O4 is then compared to F6:I6 again looking for the .001 range. In this case no values were copied.

I6 is the last comparison made to O4. The next comparisons would be between O5 and F7:I11. O5 corresponds to the K5 date10/10/99 so we look at dates in Column A (A7:A11) 10/11/00-10/15/99.

***There shouldn't be a case where when comparing the L, M, N, and O values to the B through I values where more than one value is copied by the way.

I tried to be as detailed as possible, but if there's please let me know. Appreciate the assistance on this.

EDIT: Perhaps this will help. Column Q compares M to B, C, and D. Column R compares L to B, C, D, and E. Column S compares N to G, H, and I. Column T compares O to F, G, H and I.

Ok, not the most efficient code but try this. I don't get exactly the same results as your sample. I get 1.4672 in T6 and 1.5011 in R13. Please check whether the error is in your example or the macro.

Sub x()

Dim rCell As Range, rng As Range, r1 As Range

Set r1 = Range("A4")
For Each rCell In Range("K4", Range("K4").End(xlDown))
    Do Until r1 >= rCell
        Set r1 = r1.Offset(1)
    Loop
    Set rng = r1
    Do While rng <= rCell.Offset(1)
        ' Q
        If Abs(rng.Offset(, 1) - rCell.Offset(, 2)) <= 0.001 Then rng.Offset(, 16) = rng.Offset(, 1)
        If Abs(rng.Offset(, 2) - rCell.Offset(, 2)) <= 0.001 Then rng.Offset(, 16) = rng.Offset(, 2)
        If Abs(rng.Offset(, 3) - rCell.Offset(, 2)) <= 0.001 Then rng.Offset(, 16) = rng.Offset(, 3)
        ' R
        If Abs(rng.Offset(, 1) - rCell.Offset(, 1)) <= 0.001 Then rng.Offset(, 17) = rng.Offset(, 1)
        If Abs(rng.Offset(, 2) - rCell.Offset(, 1)) <= 0.001 Then rng.Offset(, 17) = rng.Offset(, 2)
        If Abs(rng.Offset(, 3) - rCell.Offset(, 1)) <= 0.001 Then rng.Offset(, 17) = rng.Offset(, 3)
        If Abs(rng.Offset(, 4) - rCell.Offset(, 1)) <= 0.001 Then rng.Offset(, 17) = rng.Offset(, 4)
        ' S
        If Abs(rng.Offset(, 6) - rCell.Offset(, 3)) <= 0.001 Then rng.Offset(, 18) = rng.Offset(, 6)
        If Abs(rng.Offset(, 7) - rCell.Offset(, 3)) <= 0.001 Then rng.Offset(, 18) = rng.Offset(, 7)
        If Abs(rng.Offset(, 8) - rCell.Offset(, 3)) <= 0.001 Then rng.Offset(, 18) = rng.Offset(, 8)
        ' T
        If Abs(rng.Offset(, 5) - rCell.Offset(, 4)) <= 0.001 Then rng.Offset(, 19) = rng.Offset(, 5)
        If Abs(rng.Offset(, 6) - rCell.Offset(, 4)) <= 0.001 Then rng.Offset(, 19) = rng.Offset(, 6)
        If Abs(rng.Offset(, 7) - rCell.Offset(, 4)) <= 0.001 Then rng.Offset(, 19) = rng.Offset(, 7)
        If Abs(rng.Offset(, 8) - rCell.Offset(, 4)) <= 0.001 Then rng.Offset(, 19) = rng.Offset(, 8)
                
        Set rng = rng.Offset(1)
    Loop
    Set r1 = rng
Next rCell

End Sub