VBA code for go to cell with a found value

[Petula22]

Hello,

I am trying to create a VBA code to find a value in a column based on a cell with formula TODAY and then use the application.go to.

Can you please help? Thank you.

 

[Joe4]

Welcome to the Board!

Won't Excel's built-in FIND functionality do what you want?
If you want it in VBA code, simply turn on the Macro Recorder and record yourself using it, and you will get most of the VBA code you need to do this.
Then it should simply be a matter of replacing the hard-coded lookup value you used in the example with a variable set to the current date.

If you run into any problems with that, then let us know.

 

[Petula22]

Welcome to the Board!

Won't Excel's built-in FIND functionality do what you want?
If you want it in VBA code, simply turn on the Macro Recorder and record yourself using it, and you will get most of the VBA code you need to do this.
Then it should simply be a matter of replacing the hard-coded lookup value you used in the example with a variable set to the current date.

If you run into any problems with that, then let us know.

...
Thank you, Joe4. I tried that but it did not work with a cell value only with text or number. I tried it with text and it gave me this code:

"Cells.Find(What:="text", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate"

I have a cell with a formula =today() and I need the code to find the value of the formula in the column and then go to it. If that makes sense.

 

[Joe4]

What exactly do these entries that you are looking in look like?
Are they date or text entries?

 

[Petula22]

date

 

[Joe4]

If the entries you are looking in just contains dates (formatted as dates), with no time or any other text, this macro would find the current date:

Sub MyFind()

    Dim findDate As Date
    findDate = Date

    Cells.Find(What:=findDate, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
End Sub

 

[Joe4]

Here is a version with error handling, in case it cannot find the date being searched for:

Sub MyFind()

    Dim findDate As Date
    findDate = Date

    On Error GoTo err_chk
    Cells.Find(What:=findDate, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    On Error GoTo 0
    
    Exit Sub
    
err_chk:
    If Err.Number = 91 Then
        MsgBox "Cannot find date", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub

 

[Petula22]

works perfectly, thank you so much

 

[Joe4]

You are welcome.