xls vba find method to find row that contains the current date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a User Form init to find the first row that contains the current date
(for update) else find first empty row ( for insert). The Column "A" has
dates.
Eventually, the form would prompt for a selected date.
This is a partial list of the code:

Dim FindDate As String
Dim lRow As Integer
Dim ws As Worksheet

Set ws = Worksheets("Daily Sched")

' Set Current Date as String Format
FindDate = Format(Date, "mm/dd/yyyy")

' find first row containing current date
lRow = ws.Cells.Find(What:=FindDate, _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row

If lRow = 0 Then
' find first empty row in database
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If


I get a run time error '91'.
 
If no date is found then Find returns "nothing" and getting the
row number from "nothing" doesn't work.
Also, you probably ought to add the LookIn argument.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"RCranston" <[email protected]>
wrote in message
I have a User Form init to find the first row that contains the current date
(for update) else find first empty row ( for insert). The Column "A" has
dates.
Eventually, the form would prompt for a selected date.
This is a partial list of the code:

Dim FindDate As String
Dim lRow As Integer
Dim ws As Worksheet
Set ws = Worksheets("Daily Sched")
' Set Current Date as String Format
FindDate = Format(Date, "mm/dd/yyyy")

' find first row containing current date
lRow = ws.Cells.Find(What:=FindDate, _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
If lRow = 0 Then
' find first empty row in database
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If

I get a run time error '91'.
 
The find should get a match. I think I finally found the issue but not sure
best way to correct it. The FindDate is "03/26/2007" but the xls Date value
is showing up as "3/26/2007" and therefore no match. Any suggestions??
Thanks!
 
I will try the following: FindDate = "*" & Format$(Date, "m/dd/yyyy")
Is the "m/dd/yyyy" format for both single digit and double digits months.

And what about single digit and double digits days?
The FindDate could be "11/01/2007" but the xls date would be "11/1/2007"??
 
Sub ThereIsAlwaysAnotherWay()
Dim dblFind As Double
Dim ws As Worksheet
Dim rng As Range
Dim vRow As Variant

Set ws = Worksheets("Daily Sched")
Set rng = ws.Columns("A:A").Cells
dblFind = CDbl(Date)

vRow = Application.Match(dblFind, rng, 0)
If IsError(vRow) Then
vRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If
MsgBox vRow
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"RCranston" <[email protected]>
wrote in message
I will try the following: FindDate = "*" & Format$(Date, "m/dd/yyyy")
Is the "m/dd/yyyy" format for both single digit and double digits months.

And what about single digit and double digits days?
The FindDate could be "11/01/2007" but the xls date would be "11/1/2007"??
 
Back
Top