Find with a variable
 

Having a few problems with my syntax and would appreciate any help.

I want to write a macro that will find any date I specify in my spreadsheet,
I can enter the date variable using InputBox and I can do a specific find
with the Find command but I don't know how to pass the date variable entered
into the InputBox into the Find command.

--
Regards,
Chris

Find with a variable
 

Hi , Chris

Range object has a Find method. That's what you manually use the Find
dialogbox.

Inputbox returns a string that you entered,
you can pass the string to the Find methd.

str=InputBox(message,title)

If Len(str)=0 Then Exit Sub

With Worksheets(1).Range("a1:a500")
Set c = .Find(str, lookin:=xlValues)
If Not c Is Nothing Then
' Do your Processing

End If
End With

Best Regards,

sjoo.

Find with a variable
 

Hi , Chris

Range object has a Find method. That's what you manually use the Find
dialogbox.

Inputbox returns a string that you entered,
you can pass the string to the Find methd.

str=InputBox(message,title)

If Len(str)=0 Then Exit Sub

With Worksheets(1).Range("a1:a500")
Set c = .Find(str, lookin:=xlValues)
If Not c Is Nothing Then
' Do your Processing

End If
End With

Best Regards,

sjoo.

Find with a variable
 

Sometimes VBA and dates don't play nice:

Option Explicit
Sub testme01()

Dim myDate As String
Dim FoundCell As Range
Dim RngToSearch As Range

myDate = InputBox(Prompt:="enter a date")
If myDate = "" Then
Exit Sub
End If

If Year(CDate(myDate)) < 1990 _
Or Year(CDate(myDate)) 2010 Then
MsgBox "Please enter a nice date"
Exit Sub
End If

With Worksheets("sheet1")
Set RngToSearch = .UsedRange
With RngToSearch
Set FoundCell = .Cells.Find(what:=CDate(myDate), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox Format(myDate, "mmmm dd, yyyy") & " was not found"
Else
MsgBox "Found it: " & FoundCell.Address(0, 0)
End If

End Sub

If this doesn't work, you could try changing:
..Cells.Find(what:=CDate(myDate), _
to
..Cells.Find(what:=clng(CDate(myDate)), _



Chris wrote:

Having a few problems with my syntax and would appreciate any help.

I want to write a macro that will find any date I specify in my spreadsheet,
I can enter the date variable using InputBox and I can do a specific find
with the Find command but I don't know how to pass the date variable entered
into the InputBox into the Find command.

--
Regards,
Chris

--

Dave Peterson