Macro Script needed

[kshitij_dch]

hi all ,

I have a column AI in which dates are given in (MM/DD/YYYY)

I am looking for a macro which selects all the dates in Column AI apart from today's date .

If anyone can help !!!

 

[My Aswer Is This]

What does this mean:
"apart from today's date ."

Does that mean any date which is "not" todays date?

Select them and do what?
<strike></strike>

 

[kshitij_dch]

yes , all the date which is not today's date ,

Select them and against the date i need to write "coupons Given" (Next Cell)

Example :

[TABLE="width: 496"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]12/10/2015[/TD]
[TD] Coupons Given[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2016[/TD]
[TD] Coupons Given[/TD]
[/TR]
[TR]
[TD="align: right"]08/26/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/22/2017[/TD]
[TD] Coupons Given[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("AI1:AI" & Cells(Rows.Count, "AI").End(xlUp).Row)
        If IsDate(c.Value) Then If c.Value <> Date Then c.Offset(, 1).Value = "Coupons Given"
    Next
Application.ScreenUpdating = True
End Sub

 

[kshitij_dch]

thank you it is working like a charm ,

One more question ,


if in case cell is blank in column AI then i need to write "No refund" against that blank cell. ???

 

[My Aswer Is This]

If AI is empty you want AJ to say "No refund" is that what you want?

thank you it is working like a charm ,

One more question ,


if in case cell is blank in column AI then i need to write "No refund" against that blank cell. ???

 

[kshitij_dch]

yes correct .......

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 8-26-17 9:11 AM EDT
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("AI1:AI" & Cells(Rows.Count, "AI").End(xlUp).Row)
        If IsDate(c.Value) Then
            If c.Value <> Date Then c.Offset(, 1).Value = "Coupons Given"
        ElseIf c.Value = "" Then c.Offset(, 1).Value = "No Refund"
        End If
    Next
Application.ScreenUpdating = True
End Sub