Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding dates in a range
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding dates in a range
Would one message box with all the names that meet the criteria do rather than flashing individual cells? NigelShaw;574320 Wrote: 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/sh...d.php?t=158704 Microsoft Office Help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding dates in a range
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 :) NigelShaw;574320 Wrote: 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/sh...d.php?t=158704 Microsoft Office Help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding dates in a range
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 -- Jacob "NigelShaw" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
finding dates in a range
Hi Simon,
for some reason, i get a message "invalid property" and the word MyCell is highlighted directly under the word NEXT regs Nigel "Simon Lloyd" wrote: 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 :) NigelShaw;574320 Wrote: 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/sh...d.php?t=158704 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Certain Dates | New Users to Excel | |||
finding a range of dates to total | Excel Discussion (Misc queries) | |||
Finding data by dates | Excel Worksheet Functions | |||
Finding dates within a date range | Excel Worksheet Functions | |||
Finding Dates in a date range | Excel Discussion (Misc queries) |