Need code for CommandButton to read a cell, then match a cell in a range

[Roballistic]

1. My spreadsheet has a cell with the "=TODAY()" formula.
2. I have a row with calendar dates from 1/1/2020 through 12/31/2023

I want to be able to insert a CommandButton that, when clicked, either reads the cell (#1 above) and then move cursor to the matching date in the row of calendar dates (#2 above), or
if there is a simple VBA code that when clicked goes to calendar dates (#2 above) and places the cursor on the date matching today's date without looking at a cell for validation?

This is my first post too, so be gentle please, as I'm learning as I go with some of this work. Thanks for any responses. Roballistic

 

[vcoolio]

Hello Roballistic,

See if the following code assigned to a button helps:-

Sub FindADate()

     Dim c As Range

Application.ScreenUpdating = False

    For Each c In Range("A4:L4") '----> Change range to suit.
          If c.Value = [Today()] Then
                Range(c.Address).Select
          End If
    Next c

Application.ScreenUpdating = True

End Sub

If the range of date cells are merged, then the code will error.
Please test it in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.

 

[Roballistic]

VCoolioHello Roballistic,

See if the following code assigned to a button helps:-

Sub FindADate()

     Dim c As Range

Application.ScreenUpdating = False

    For Each c In Range("A4:L4") '----> Change range to suit.
          If c.Value = [Today()] Then
                Range(c.Address).Select
          End If
    Next c

Application.ScreenUpdating = True

End Sub

If the range of date cells are merged, then the code will error.
Please test it in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Vcoolio, you are awesome and thanks for being my first experience with this forum!
This did work, but wondering why when the cursor moves to an area beyond what is seen it doesn't scroll over to show me where it is sitting now? If nothing more can be done, I'll live with it, but was hopeful there might be something to add that will basically take the person's view to the cell, not just the cursor hiding somewhere throughout the workbook where it found a match?

 

[vcoolio]

Hello Roballistic,

Sorry about that. I wrongly assumed that your range was always in view. Simply remove the two "Application.ScreenUpdating" lines and the cursor will scroll to the relative cell.
Here's another slightly modified version of the same code which will do the same task:-

Sub FindADate()

     Dim c As Range

    For Each c In Range("A4:AK4") '----> Change range to suit.
          If c.Value = [Today()] Then
                c.Select
          End If
    Next c

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[Roballistic]

Hello Roballistic,

Sorry about that. I wrongly assumed that your range was always in view. Simply remove the two "Application.ScreenUpdating" lines and the cursor will scroll to the relative cell.
Here's another slightly modified version of the same code which will do the same task:-

Sub FindADate()

     Dim c As Range

    For Each c In Range("A4:AK4") '----> Change range to suit.
          If c.Value = [Today()] Then
                c.Select
          End If
    Next c

End Sub

I hope that this helps.

Cheerio,
vcoolio.

Perfection! VCoolio knows all. I'm not worthy! I'm not worthy! Thanks for all your help.

 

[vcoolio]

VCoolio knows all.

Ha. I wish!
You're welcome Roballistic. I'm glad to have been able to help and thanks for the feed-back.

Cheerio,
vcoolio.