finding dates in a range

[NigelShaw]

Hi,

This is related to VBA

i need to try and find a date in range ("C:C"). if the date =< todays date +
365 ie 12 months or less, flash a message with the name in the same row but
in column ("A:A").

firstly, is this achievable? im an access VBA person and havent done excel
for a long time
secondly, if it is, can it loop through the rows in column C until it
reaches the end and flash a message for each date hitting this condition?


hope someone an help


Nigel

 

[Simon Lloyd]

Would one message box with all the names that meet the criteria do
rather than flashing individual cells?

Hi,

This is related to VBA

i need to try and find a date in range ("C:C"). if the date =< todays
date +
365 ie 12 months or less, flash a message with the name in the same row
but
in column ("A:A").

firstly, is this achievable? im an access VBA person and havent done
excel
for a long time
secondly, if it is, can it loop through the rows in column C until it
reaches the end and flash a message for each date hitting this
condition?


hope someone an help


Nigel

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Simon Lloyd]

As an add to my last post try this Nigel:


Code:
--------------------
Sub find_date()
Dim Rng As Range, MyCell As Range, MyMsg
Set Rng = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell <= Date - 365 Then
MyMsg = MyMsg & vbLf & MyCell.Offset(0, -2).Value & " - Row " & MyCell.Row
End If
Next MyCell
MsgBox MyMsg
End Sub
--------------------
See how you go with that

Hi,

This is related to VBA

i need to try and find a date in range ("C:C"). if the date =< todays
date +
365 ie 12 months or less, flash a message with the name in the same row
but
in column ("A:A").

firstly, is this achievable? im an access VBA person and havent done
excel
for a long time
secondly, if it is, can it loop through the rows in column C until it
reaches the end and flash a message for each date hitting this
condition?


hope someone an help


Nigel

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub MyMacro()
Dim lngRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("C" & lngRow) > 0 Then
If DateDiff("d", Range("C" & lngRow), Date) <= 365 Then
MsgBox Range("A" & lngRow), , "Less then 1 Year"
End If
End If
Next
End Sub

 

[NigelShaw]

Hi Simon,

for some reason, i get a message "invalid property" and the word MyCell is
highlighted directly under the word NEXT

regs

Nigel

As an add to my last post try this Nigel:


Code:
--------------------
Sub find_date()
Dim Rng As Range, MyCell As Range, MyMsg
Set Rng = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell <= Date - 365 Then
MyMsg = MyMsg & vbLf & MyCell.Offset(0, -2).Value & " - Row " & MyCell.Row
End If
Next MyCell
MsgBox MyMsg
End Sub
--------------------
See how you go with that

Hi,

This is related to VBA

i need to try and find a date in range ("C:C"). if the date =< todays
date +
365 ie 12 months or less, flash a message with the name in the same row
but
in column ("A:A").

firstly, is this achievable? im an access VBA person and havent done
excel
for a long time
secondly, if it is, can it loop through the rows in column C until it
reaches the end and flash a message for each date hitting this
condition?


hope someone an help


Nigel

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=158704

Microsoft Office Help

.