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

Roballistic

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  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
 

Attachments

  • CommandButtonHelp.JPG
    CommandButtonHelp.JPG
    79.5 KB · Views: 7

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,056
Hello Roballistic,

See if the following code assigned to a button helps:-
VBA Code:
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

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
VCoolioHello Roballistic,

See if the following code assigned to a button helps:-
VBA Code:
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,056
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:-

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

Roballistic

New Member
Joined
Jan 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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:-

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

Watch MrExcel Video

Forum statistics

Threads
1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

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
Top