Macro Help, Goto Certain Date

[Droether]

Hi guys,

I'm new to Macro's etc but have to sort something for work that requires Macro's. I'm slowly trying to get my head round it by doing basic things that slowly get more complex - walk before I run!

Anywho, the first thing I need to do, is a button that'll take me to the current date. My spreadsheet is set out so I have the list of dates in column A (Cells A6-A561), and a =TODAY() formula in cell B5. How can I get it to go to the date in ColumnA, that's shown in B5?

I've done some googling, and so far have the following. It may be horrifically wrong, but I'm not sure!

Public Sub Today()


myvalue = Application.Match(CLng(B5), Range("A6:A561"), 0)

Application.Goto rGoTo, True
On Error Resume Next
End Sub

I *think* that should match B5 to one of the cells in that range? How do I then make it go to that result?

Like I said, I'm new to this and any help is greatly appreciated.

Cheers!

 

[GTO]

Greetings,

I'm not sure where rGoTo comes from, as at least in the procedure supplied, it is not declared. Neither is myvalue.

Guessing that you may not have these declared, my first suggestion would be to use Option Explicit. When included at the top of each module, this insists upon variable declaration. So it will tell you when you forget to declare a variable, and more importantly, catch when you miss spell a variable. In short, a real headache saver.

See if this helps. In short, we want to bypass errors only for getting by .Match, in case there is no match. You do not want to leave On Error Resume Next in effect for any longer than absolutely necessary - it hides errors and you'll have difficulty finding them.

Option Explicit
    
Sub exa()
Dim lRow As Long
Dim rng As Range
    
    With Sheet1 '<--- OR ThisWorkbook.Worksheets("Your sheet's tab name")
        Set rng = .Range("A6:A561")
        On Error Resume Next
        lRow = Application.Match(CLng(.Cells(5, "B").Value), rng, 0)
        On Error GoTo 0
        
        If lRow > 0 Then
            lRow = lRow + rng.Row - 1
            Application.Goto .Cells(lRow, 1)
        End If
    End With
End Sub

 

[GTO]

PS - I forgot to mention. To automatically include Option Explicit in new modules, whilst in VBIDE (the code window), go to Tools|Options...|Editor tab. Tick the 'Require Variable Declaration' checkbox.