Assigning values to dates

[ChipButtyMan]

Hi everyone,

"C"
"J" "K"
14/10/2008 01/10/2008 1
01/10/2008 06/10/2008 2
14/10/2008 07/10/2008 3
07/10/2008 13/10/2008 4
07/10/2008 14/10/2008 5
06/10/2008 18/10/2008 6
01/10/2008
13/10/2008
18/10/2008



Column "B" is a list of dates in no particular order.
Column "J" is a filtered list of those dates
Column "K" is a list of values assigned to the different dates.
How do I write the code to assign those values in column "K" to the
dates in column "C"
(they would be returned to the adjacent column "D")

thank you for your help, your time & your and expertise.

 

[ChipButtyMan]

Hi everyone,

     

"C" J"    
     "K"

14/10/2008                                      01/10/2008      1
01/10/2008                                      06/10/2008      2
14/10/2008                                      07/10/2008      3
07/10/2008                                      13/10/2008      4
07/10/2008                                      14/10/2008      5
06/10/2008                                      18/10/2008      6
01/10/2008
13/10/2008
18/10/2008

Column "B" is a list of dates in no particular order.
Column "J" is a filtered list of those dates
Column "K" is a list of values assigned to the different dates.
How do I write the code to assign those values in column "K" to the
dates in column "C"
(they would be returned to the adjacent column "D")

thank you for your help, your time & your and expertise.

I have repositioned the column labels!

 

[Mike H]

Hi,

Try this in C1 and drag down

=VLOOKUP(B1,$J$1:$K$6,2,FALSE)

Mike

 

[Mike H]

Apologies, You wanted code

Right click your sheet tab, view code and paste this in and run it.

Sub DateCodes()
Dim MyRangeB As Range, MyRangeJ As Range , B As Range, J As Range
Dim LastRowB As Long, LastrowJ As Long
LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
LastrowJ = Cells(Rows.Count, "J").End(xlUp).Row
Set MyRangeJ = Range("J1:J" & LastrowJ)
Set MyRangeB = Range("B1:B" & LastRowB)
For Each B In MyRangeB
For Each J In MyRangeJ
If B.Value = J.Value Then
B.Offset(, 1).Value = J.Offset(, 1).Value
End If
Next
Next
End Sub


Mike

 

[ChipButtyMan]

Apologies, You wanted code

Right click your sheet tab, view code and paste this in and run it.

Sub DateCodes()
Dim MyRangeB As Range, MyRangeJ As Range , B As Range, J As Range
Dim LastRowB As Long, LastrowJ As Long
LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
LastrowJ = Cells(Rows.Count, "J").End(xlUp).Row
Set MyRangeJ = Range("J1:J" & LastrowJ)
Set MyRangeB = Range("B1:B" & LastRowB)
For Each B In MyRangeB
    For Each J In MyRangeJ
        If B.Value = J.Value Then
        B.Offset(, 1).Value = J.Offset(, 1).Value
        End If
    Next
Next
End Sub

Mike







- Show quoted text -

Wow! Thanks ever so much for your time Mike. Works perfectly.
I spent all last night trying to work it out & failed miserably.
Thanks again. You made my day

 

[ChipButtyMan]

Thanks ever so much Mike.
You just made my day.
Spent an age trying to work it out and failed miserably.
Thanks again.

 

[mohithsunder]

Hi everyone,

     "C"
"J"          "K"
14/10/2008                                      01/10/2008      1
01/10/2008                                      06/10/2008      2
14/10/2008                                      07/10/2008      3
07/10/2008                                      13/10/2008      4
07/10/2008                                      14/10/2008      5
06/10/2008                                      18/10/2008      6
01/10/2008
13/10/2008
18/10/2008

Column "B" is a list of dates in no particular order.
Column "J" is a filtered list of those dates
Column "K" is a list of values assigned to the different dates.
How do I write the code to assign those values in column "K" to the
dates in column "C"
(they would be returned to the adjacent column "D")

thank you for your help, your time & your and expertise.

Hi Paul

You need to use the vlookup function to get this done .
Here is how to use it
=VLOOKUP(C1,$J$1:$K$7,2,FALSE)
C1 -> Refers to the value which is in column "C"
$J$1:$K$7 -> Refers to the table array from where you need to choose
the value
2 -> is the column number in the table
Just let me know if you need any more help

 

[ShaneDevenshire]

Hi,

Since you didn't specify the where you data starts in column C or whether it
is solid or whether there is data below it with is not part of the data, here
is a really simple, and extremely fast macro:

Sub LookupStuff()
Selection = "=VLOOKUP(C2,K$2:J$10,2,FALSE)"
Selection = Selection.Value
End Sub

In this case the first cell to be looked up is in C2 and the lookup range is
in K2:J10, you can adjust the code to meet your needs. To use this just
select the cells in column D which you want populated and run the macro.