identify whic row contains todays date

[John Keith]

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
(e-mail address removed)

 

[OssieMac]

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]

On Fri, 24 Apr 2009 20:03:01 -0700, OssieMac

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
(e-mail address removed)

 

[OssieMac]

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.

 

[OssieMac]

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

 

[John Keith]

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
(e-mail address removed)

 

[Mr. Haney]

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.

 

[John Keith]

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[Chip Pearson]

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)

 

[John Keith]

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.

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)

John Keith
(e-mail address removed)