Find function referencing a cell value

[nbh45]

I'm trying to create a macro that will take the user to the current day in a large excel calendar when they click a button. I'm currently attempting to do this using the find function by having it look up the current date in a cell in the worksheet, which date is updated using the TODAY() formula. The current date (i.e., the TODAY() formula) is in cell F4, and the calendar is in cells C13:J1000.

Anyway, I'm very new to VBA, but below is my attempt. If I enter in a day that I want, for example "Wednesday, August 03, 2011" I can get it to work, but I would like it to automatically update so other users can simply click the button to go to the current day. However, I have been unable to get that to work so far - the "Not Found" message box is all that gets returned.

Any suggestions or ideas would be greatly appreciated. Thanks!

Sub Findinrange1()

Range("C13:J1000").Select

mystring = ActiveSheet.Cells(4, 6).Value
Set RangeObj = Selection.Find(What:=mystring, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If RangeObj Is Nothing Then MsgBox "Not Found" Else RangeObj.Select
End Sub

 

[Marcelo Branco]

Hi,

Try this

Sub Findinrange1()
    Dim myDate As Date
    Dim RangeObj As Range
 
    myDate = ActiveSheet.Cells(4, 6).Value
    Set RangeObj = Range("C13:J1000").Find(What:=myDate, After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    If RangeObj Is Nothing Then MsgBox "Not Found" Else RangeObj.Select
End Sub

HTH

M.

 

[nbh45]

Hi Marcelo,

Thanks for the idea. Unfortunately, that returns a run-time error 12 - type mismatch. Thanks though!

 

[Marcelo Branco]

Hi Marcelo,

Thanks for the idea. Unfortunately, that returns a run-time error 12 - type mismatch. Thanks though!

Do you have real-dates in your calendar or strings (text)?

If you have the dates as text change the
Dim myDate as Date

to
Dim myDate as String

M.

 

[nbh45]

They are real dates. The user inputs the first Monday of the calendar and then the remaining dates are calculated off that date. No luck trying to evaluate as a String - still get the 'type mismatch' error.

-N

 

[Marcelo Branco]

They are real dates. The user inputs the first Monday of the calendar and then the remaining dates are calculated off that date. No luck trying to evaluate as a String - still get the 'type mismatch' error.

-N

hmmm...

It worked for me with real dates and
Dim myDate as Date

I dont know why it is not working for you...

M.

 

[Marcelo Branco]

Try change this
After:=ActiveCell

to

After:=Range("C13")

M.