Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
In column B I have a long list of sequential dates like below:
4/15/09 4/16/09 4/17/09 4/18/09 .. .. .. 4/30/09 5/1/09 and so on. In a macro I would like to identify which row contains todays date so that I can perform some additional function on cells to the right of this date. I've tried a loop that searchs through column B like below but obviously I don't have the right test: If cells(i,2) = format(now(), "mm/dd/yy") then I think the Find function might also work but I haven't a clue how to do this. Suggestions would be greatly appreciated. TIA John Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Hi John,
Lookup FindNext in Help if you need to find more than one occurrence of today's date. Sub FindToday() Dim dateToday As Date Dim rngColB As Range Dim rngToFind As Range dateToday = Date With Sheets("Sheet1") Set rngColB = .Range("B:B") End With With rngColB Set rngToFind = .Find(What:=dateToday, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With If Not rngToFind Is Nothing Then 'insert your code here in lieu of 'following 2 lines if date found rngToFind.Select MsgBox "Found value" Else MsgBox "Did not find value" Exit Sub End If End Sub -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
On Fri, 24 Apr 2009 20:03:01 -0700, OssieMac
wrote: OssieMac, Thanks, that finds the correct cell with todays date (and I'll only have one entry for todays date so I don't need the FindNext right now.) So if I want to reference the contents of the cell in column C of this same row how do I address it? cells(?, "C") Hi John, Lookup FindNext in Help if you need to find more than one occurrence of today's date. Sub FindToday() Dim dateToday As Date Dim rngColB As Range Dim rngToFind As Range dateToday = Date With Sheets("Sheet1") Set rngColB = .Range("B:B") End With With rngColB Set rngToFind = .Find(What:=dateToday, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With If Not rngToFind Is Nothing Then 'insert your code here in lieu of 'following 2 lines if date found rngToFind.Select MsgBox "Found value" Else MsgBox "Did not find value" Exit Sub End If End Sub John Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Hi again John,
Dim lngRow As Long Dim lngCol As Long Dim varValue As Variant lngRow = rngToFind.Row 'Returns the row number lngCol = rngToFind.Column 'Returns the column number varValue = c.Value 'Returns the value in the cell Cells(lngRow, "C") = c.Value 'Copies c.value to column C same row c.Offset(0, 3).Select 'Addresses a column 3 to right c.Offset(0, -3).Select 'Addresses a column 3 to left c.Offset(3, 0).Select 'Addresses a row 3 down c.Offset(-3, 0).Select 'Addresses a row 3 to up 'You don't have to select the cell when using Offset. Example:- c.offset(0,-3).value = c.value 'Copies c.value to cell 3 to left GUIDE for Offset:_ The offset number is the number of times you would have to press the right/left/down or up arrow to get to the required position. Hope this helps. -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Sorry John there is an error in the previous post.
Wherever I have use c. then change that to rngToFind. Amended code here. Dim lngRow As Long Dim lngCol As Long Dim varValue As Variant lngRow = rngToFind.Row 'Returns the row number lngCol = rngToFind.Column 'Returns the column number varValue = rngToFind.Value 'Returns the value in the cell Cells(lngRow, "C") = rngToFind.Value 'Copies rngToFind.value to column C same row rngToFind.Offset(0, 3).Select 'Addresses a column 3 to right rngToFind.Offset(0, -3).Select 'Addresses a column 3 to left rngToFind.Offset(3, 0).Select 'Addresses a row 3 down rngToFind.Offset(-3, 0).Select 'Addresses a row 3 to up 'You don't have to select the cell when using Offset. Example:- rngToFind.offset(0,-3).value = c.value 'Copies rngToFind.value to cell 3 to left GUIDE for Offset:_ The offset number is the number of times you would have to press the right/left/down or up arrow to get to the required position -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
On Fri, 24 Apr 2009 21:40:04 -0700, OssieMac
wrote: Sorry John there is an error in the previous post. Hmmm, shows you how much I know about VBA yet, I tried your original code and it seemed to work, at least the variable was set to todays date. In any case your second post provided much detail that explains the functionality (and more) that I need. Thank oyu very much and have a good day. John Keith |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Jeez, I wish you would examine my "Tally my bananas" post. I am sure the solution I need is simple. The last post in the thread is the most informative. On Fri, 24 Apr 2009 21:40:04 -0700, OssieMac wrote: Sorry John there is an error in the previous post. Wherever I have use c. then change that to rngToFind. Amended code here. Dim lngRow As Long Dim lngCol As Long Dim varValue As Variant lngRow = rngToFind.Row 'Returns the row number lngCol = rngToFind.Column 'Returns the column number varValue = rngToFind.Value 'Returns the value in the cell Cells(lngRow, "C") = rngToFind.Value 'Copies rngToFind.value to column C same row rngToFind.Offset(0, 3).Select 'Addresses a column 3 to right rngToFind.Offset(0, -3).Select 'Addresses a column 3 to left rngToFind.Offset(3, 0).Select 'Addresses a row 3 down rngToFind.Offset(-3, 0).Select 'Addresses a row 3 to up 'You don't have to select the cell when using Offset. Example:- rngToFind.offset(0,-3).value = c.value 'Copies rngToFind.value to cell 3 to left GUIDE for Offset:_ The offset number is the number of times you would have to press the right/left/down or up arrow to get to the required position |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
On Fri, 24 Apr 2009 21:40:04 -0700, OssieMac
wrote: Sorry John there is an error in the previous post. OssieMac, I want to thank you again for the detailed expansion you provided and I now understand why I was a little confused about your correction. For some reason my reader did not pull your first post that expanded on your solution so I thought your comment about an error refered to the first post you made. Now it all makes sense and I am very happy that I can move on with my task! John Keith |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Follow up question:
I have made great progress on the macro I'm creating but I tried to make one change and something broke. I originally had dates as values in column B, but it occured to me that a formula to generate the date might be useful for future purposes. So rather than enter 4/10/09 and so on into each cell for the moment I put 4/10/09 into cell B2 and in cell B3 and below I put the formula =B2 +1 and copied this formula down the column. Additional I change the LookIn spec of the Find statement to: LookIn:=xlValues. But this hacking has broken the Find function as it no longer locates today's date. Any suggestions on what I'm not doing cirrectly? With rngColB Set rngToFind = .Find(What:=dateToday, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With John Keith |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
If the following are true:
1) the first cell in the list of dates is earlier than the current date, 2) dates are sequential with no skipped dates or blank cells, 3) the last cell in the list is greater than the current date, you can use Dim StartCell As Range Dim TodayCell As Range Set StartCell = Range("B3") Set TodayCell = StartCell(CLng(Now) - CLng(StartCell), 1) where B3 is the cell with the first date. The TodayCell will be set to the cell in column B containing today's date. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 24 Apr 2009 20:13:00 -0600, John Keith wrote: In column B I have a long list of sequential dates like below: 4/15/09 4/16/09 4/17/09 4/18/09 . . . 4/30/09 5/1/09 and so on. In a macro I would like to identify which row contains todays date so that I can perform some additional function on cells to the right of this date. I've tried a loop that searchs through column B like below but obviously I don't have the right test: If cells(i,2) = format(now(), "mm/dd/yy") then I think the Find function might also work but I haven't a clue how to do this. Suggestions would be greatly appreciated. TIA John Keith |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
identify whic row contains todays date
Chip,
Thank you for the suggestion. My current data does not meet criteria 2) as you note below but the more I think about the problem I'm realizing that my date data probably needs to be sequential, in fact the previous solution provided by OssieMac will also have a problem with my current structure. CLng is new to me. I'll have to play with this to see how it works. It is certainly a very simple solution. On Sat, 25 Apr 2009 15:14:04 -0500, Chip Pearson wrote: If the following are true: 1) the first cell in the list of dates is earlier than the current date, 2) dates are sequential with no skipped dates or blank cells, 3) the last cell in the list is greater than the current date, you can use Dim StartCell As Range Dim TodayCell As Range Set StartCell = Range("B3") Set TodayCell = StartCell(CLng(Now) - CLng(StartCell), 1) where B3 is the cell with the first date. The TodayCell will be set to the cell in column B containing today's date. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 24 Apr 2009 20:13:00 -0600, John Keith wrote: In column B I have a long list of sequential dates like below: 4/15/09 4/16/09 4/17/09 4/18/09 . . . 4/30/09 5/1/09 and so on. In a macro I would like to identify which row contains todays date so that I can perform some additional function on cells to the right of this date. I've tried a loop that searchs through column B like below but obviously I don't have the right test: If cells(i,2) = format(now(), "mm/dd/yy") then I think the Find function might also work but I haven't a clue how to do this. Suggestions would be greatly appreciated. TIA John Keith John Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there a formula that will subtract todays date from a hire date | Excel Worksheet Functions | |||
Making a date go red, if date passes todays date. | Excel Worksheet Functions | |||
Exel increment date problem wrt todays date. | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
When I open my past invoice it keeps changing date to todays date | Excel Worksheet Functions |