Macro to Go To an intended date in a filtered range

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,513
Office Version
  1. 2024
Platform
  1. Windows
Hi Experts
Recently, I got help from @kevin9999 in the following post


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 -

VBA Code:
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
 
Give this a try.
VBA Code:
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
@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.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
One more try for today :)

VBA Code:
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
 
Upvote 0
Solution
Hi Experts
Recently, I got help from @kevin9999 in the following post


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 -

VBA Code:
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 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...
 
Upvote 0

Attachments

  • Screenshot 2022-12-19 at 15.35.24.png
    Screenshot 2022-12-19 at 15.35.24.png
    22.5 KB · Views: 5
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
in the above line (code) changed C1 to C6 and it worked.

Glad to hear you got it working Sanjay (y)
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top