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

[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'.

 

[Jim Cone]

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'.

 

[Guest]

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!

 

[Jim Cone]

FindDate = "*" & Format$(Date, "m/dd/yyyy")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"RCranston" <[email protected]>
wrote in message
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!

 

[Guest]

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"??

 

[Jim Cone]

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"??