Macro to Go To an intended date in a filtered range

[SanjayGMusafir]

Hi Experts
Recently, I got help from @kevin9999 in the following post

VBA to Go to Today in a filtered range

Hi Experts I use following code to jump to today in a filtered range. It works well when table is sorted by date first and then by any other field. But challenge arises when table is sorted first by any other field and then by date. In such cases present date(s) can be around 1000+ rows...

www.mrexcel.com

Once I got things working, I thought exploring options and adding more conditions to it.

Now I realized that instead of jumping to closest date for example in my case - rather than jumping to 20Nov (today-30) it jumps to 25 Sep (today-90)

I must have ignored something simple and unable to identify it.

Please help

Thanks a lot

The code I'm using right now is -

Dim x As Range
        For Each x In Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp))
            If x >= Date And x <= Date + 365 And x.EntireRow.Hidden = False Then
                x.Select
                Exit For
            ElseIf x >= Date - 30 And x <= Date And x.EntireRow.Hidden = False Then
                x.Select
                Exit For
            ElseIf x >= Date - 60 And x <= Date - 30 And x.EntireRow.Hidden = False Then
                x.Select
                Exit For
            ElseIf x >= Date - 90 And x <= Date - 60 And x.EntireRow.Hidden = False Then
                x.Select
                Exit For
            End If
        Next x

 

[kevin9999]

Hi Sanjay, could we see your actual data with an XL2BB sample? Based on what you've said, it's jumping to the 4th conditional match your code looks at?

 

[SanjayGMusafir]

Hi Sanjay, could we see your actual data with an XL2BB sample? Based on what you've said, it's jumping to the 4th conditional match your code looks at?

@kevin9999 Unfortunately it's a huge database to share XL2BB else I would have

Underneath is a filtered range screenshot where I realised that it was directly jumping to the 4th condition directly.

It would be great if you may help.

Thanks a lot

 

[SanjayGMusafir]

Hi Sanjay, could we see your actual data with an XL2BB sample? Based on what you've said, it's jumping to the 4th conditional match your code looks at?

Here's another screenshot to understand

 

[SanjayGMusafir]

Hi Sanjay, could we see your actual data with an XL2BB sample? Based on what you've said, it's jumping to the 4th conditional match your code looks at?

Just for your understanding @kevin9999 - If I use ' sign before 4 the condition then it select cell that fulfills 3rd condition and so on...

I wonder what mistake I might be doing in syntax or ordering of conditions...

 

[kevin9999]

Just out of interest, why do you expect it to jump to 20 November?

 

[SanjayGMusafir]

Just out of interest, why do you expect it to jump to 20 November?

as close as today - whichever way we may be able to get it

Thanks a lot

 

[kevin9999]

Are your dates constants, or the result of formulas?

 

[SanjayGMusafir]

Are your dates constants, or the result of formulas?

All past dates are constant

I occasionally use Today()+1 to record some future transactions and them turn them to constant as and when they happen

 

[kevin9999]

Give this a try.

Sub Sanjay()
    Dim a As Range, d As Date
    With Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)
        d = Date
        For d = Date To Date - 10 Step -1   'Is there ever likely to be more than a 10 day gap?
            If Not .Find(d, After:=Range("C2")) Is Nothing Then
                Set a = .Find(d, After:=Range("C2"))
                a.Select
                Exit For
            End If
        Next d
    End With
End Sub