# Macro to Go To an intended date in a filtered range



## SanjayGMusafir (Dec 19, 2022)

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 (Dec 19, 2022)

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 (Dec 19, 2022)

kevin9999 said:


> 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 (Dec 19, 2022)

kevin9999 said:


> 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 (Dec 19, 2022)

kevin9999 said:


> 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 (Dec 19, 2022)

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


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> 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 (Dec 19, 2022)

Are your dates constants, or the result of formulas?


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> 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 (Dec 19, 2022)

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
```


----------



## SanjayGMusafir (Dec 19, 2022)

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
```


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> Give this a try.
> 
> ```
> Sub Sanjay()
> ...


@kevin9999 The above doesn't seem to be working at all

Also in my case gaps can be very variable at times months and times daily transactions

The above code I tried with same sample data that I gave you in screenshot but it didn't work. Also it didn't work on unfiltered data too.


----------



## kevin9999 (Dec 19, 2022)

One more try for today 


```
Option Explicit
Sub Sanjay_2()
    Dim ar, i As Long, j As Long, ws As Worksheet, LRow As Long
    Set ws = Worksheets("Bank")
    LRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    ar = ws.Range("C1", Cells(Rows.Count, "C").End(xlUp))
    
    For i = 1 To UBound(ar, 1)
        If ar(i, 1) = CLng(Date) And Cells(i, 3).EntireRow.Hidden = False Then
            Cells(i, 3).Select
            Exit Sub
        ElseIf ar(i, 1) > CLng(Date) Then
            For j = 1 To (LRow - 1)
                If Cells(i - j, 3).EntireRow.Hidden = False Then
                    Cells(i - j, 3).Select
                    Exit Sub
                End If
            Next j
        End If
    Next i
End Sub
```


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> If ar(i, 1) = CLng(Date) And Cells(i, 3).EntireRow.Hidden = False Then


Gave error on this part of code


----------



## kevin9999 (Dec 19, 2022)

SanjayGMusafir said:


> Gave error on this part of code


What did the error say?


----------



## SanjayGMusafir (Dec 19, 2022)

SanjayGMusafir said:


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


@kevin9999 Just a food for thought

Are we doing something wrong in this code? It's not ending at the right spot and jumping to the next condition...


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> What did the error say?


----------



## kevin9999 (Dec 19, 2022)

OK. I can't go any further until you give me a sample of your actual sheet using the XL2BB add in.  It doesn't have to be a huge amount, just the range you had in the images you've already posted will do.  As a point of interest, the code worked for me based on what I _think _your actual data/structure looks like.


----------



## SanjayGMusafir (Dec 19, 2022)

kevin9999 said:


> ar = ws.Range("C1", Cells(Rows.Count, "C").End(xlUp))


Hi @kevin9999 
An important feedback

Actually I tired to understand what this macro was doing. Then realized my table starts from cell C6. C5 being heading and 4 top rows freezed.

So, in the above line (code) changed C1 to C6 and it worked.

Most of the times it takes me closest to today.

But occasionally it it takes me to day before today even when current date is available.

Can you explain why it does that? Or help me with that?


----------



## kevin9999 (Dec 19, 2022)

SanjayGMusafir said:


> in the above line (code) changed C1 to C6 and it worked.



Glad to hear you got it working Sanjay   


SanjayGMusafir said:


> But occasionally it it takes me to day before today even when current date is available.


I can't explain that.  The code uses the reserved word *Date *to return the _current _date of your device's internal clock - so the input variable doesn't change each time.  If your data also doesn't change (in value *or* visibility - remember the row must not be hidden) then there's no reason I can think of why you're getting different results occasionally.


----------



## SanjayGMusafir (Dec 19, 2022)

Thanks again @kevin9999 
It creates a better understanding for me for sure.


----------

