Compare two columns and populate the third

[rollingzep]

Hi,

<p> I have two dates, col B and col AY. I need to populate the value in col BB as 1, if value of col AY is equal or less than 30 days of col B.
If it is more than 30 days, it should do nothing as shown in row 3 and 4.

<code>

firstDate = ws.Range("B" & Rows.Count).End(xlUp).Row
secondDate = ws.Range("AY" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If DateDiff("d", firstDate, secondDate) <= 30 Then
ws.Range("BB" & i).Value = "1"
End If
Next i

</code>

It is putting all values in BB as 1. I think i need the code to read the dates through all the rows in col B and col AY .
I need help with my code </p>

Thanks

 

[Zot]

Try this

Sub DateTest()

Dim i As Long, LastRow As Long
Dim FirstDate As Date, SecondDate As Date
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")    ' Change sheet name accordingly

LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    FirstDate = ws.Range("B" & i)
    SecondDate = ws.Range("AY" & i)
    If DateDiff("d", FirstDate, SecondDate) <= 30 Then ws.Range("BB" & i) = 1
Next

End Sub

 

[rollingzep]

Try this

Sub DateTest()

Dim i As Long, LastRow As Long
Dim FirstDate As Date, SecondDate As Date
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")    ' Change sheet name accordingly

LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    FirstDate = ws.Range("B" & i)
    SecondDate = ws.Range("AY" & i)
    If DateDiff("d", FirstDate, SecondDate) <= 30 Then ws.Range("BB" & i) = 1
Next

End Sub

Worked perfectly, Thanks!