Updating cells based on another

[KW]

I have a spreadsheet with various dates as shown below

Col V Col W Col X Col Y
26-Feb-09 1 26-Mar-09 1
01-Feb-09 12 01-Feb-10 0
27-Feb-09 1 27-Mar-09 1
19-Feb-09 3 19-May-09 0
20-Feb-09 3 20-May-09 0

I want to replace the date in Col V with the date in Col X when Col Y = '1'. As Col X contains a formulae, I only want to be able to copy and the cell value.

I've tried filtering Col Y for '1', but as the resulting cells are non-adjacent, I can't copy and paste the resulting selection. Perhaps a simple macro might work, but I could really do with some guidance.

Regards

KW

 

[Prabby1981]

Think this should do :

Sub UpdateDate()

Dim LRow As Long

LRow = Application.Range("X65536").End(xlUp).Row

For i = 2 To LRow

    If Range("Y" & i).Value = 1 Then
        Range("X" & i).Copy
        Range("V" & i).PasteSpecial (xlValues)
    End If

Next

MsgBox "Done !"

End Sub

Hope this helps !

 

[KW]

Thanks Prabby. You're a top man