Jump to cell using a date picker

[robocop]

Hello to all the excel gurus out there! I have a dilemma and hoping someone can help me out.

I have a spreadsheet of data that has dates with information listed under each date. Is there a code that will allow me to enter a date (using a date picker) in a selected cell and it will jump to that column based on the date i selected instead of having to scroll over?

For example: Using A1 as the designated date picker location, i would like to select a date (4/30/17) , the cursor will jump over to AH1 . Hope this makes sense.

	A	B	C	D	AH
1	4/30/17	1/13/17	1/14/17	1/15/17	4/30/17
2		$200	$400	$500	$600
3		$400	$500	$600	$700
4		$10	$20	$30	$50
5		$15	$25	$40	$55

 

[Joe4]

Sure. Are you always entering the date you are looking for in column A?
And, is the date you are looking for always on the same row that you entered the date on?
What do you want to happen if it cannot find the date?

 

[DanteAmor]

Put the following code in sheets events.
Every time you modify cell A1 it will select the cell with the date

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.CountLarge > 1 Then Exit Sub
    Dim f As Range
    Set f = Range("B1", Cells(1, Columns.Count)).Find(Target, , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      f.Select
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[robocop]

Sure. Are you always entering the date you are looking for in column A?
And, is the date you are looking for always on the same row that you entered the date on?
What do you want to happen if it cannot find the date?

Hello DA..thanks for replying
1. Yes, i would like to only use cell A1 and a date picker drop down calendar to select the date. Once date is selected, it will move to that cell and column.
2. Yes, dates will be in the same row that i entered the date on.
3. If cannot find the date, stay in current selected cell.

 

[Joe4]

Does Dante's code do what you want?

Is it always going to be just row 1, or possibly other rows too?

 

[robocop]

Does Dante's code do what you want?

Is it always going to be just row 1, or possibly other rows too?

it can possibly be other rows but im hope to only be using row 1

 

[Joe4]

Try the code that Dante posted then. I think it might do exactly what you want.

 

[robocop]

Put the following code in sheets events.
Every time you modify cell A1 it will select the cell with the date

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Then
    If Target.CountLarge > 1 Then Exit Sub
    Dim f As Range
    Set f = Range("B1", Cells(1, Columns.Count)).Find(Target, , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      f.Select
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Thank you Dante, the code is very close to what im looking for. Is it possible to modify cell A1 using a drop down date picker? And, once the date is selected it will scroll the selected column with date next to A1...Hope that makes sense

 

[Joe4]

Assuming that you always want the Date Picker in there, you probably don't adding that as part of the VBA code (or else it won't be a Date Picker field until the VBA code is run).
Here is how you can add one: How to create calendar in Excel (drop-down and printable)

Dante's VBA code will run regardless of whether A1 is a Date Picker or not. It simply automatically runs on any change to the value in cell A1.
If you put it in the peoper Sheet module, it will run automtically anytime that cell A1 is updated, and should do what you want.

 

[robocop]

Assuming that you always want the Date Picker in there, you probably don't adding that as part of the VBA code (or else it won't be a Date Picker field until the VBA code is run).
Here is how you can add one: How to create calendar in Excel (drop-down and printable)

Dante's VBA code will run regardless of whether A1 is a Date Picker or not. It simply automatically runs on any change to the value in cell A1.
If you put it in the peoper Sheet module, it will run automtically anytime that cell A1 is updated, and should do what you want.

Okay! thanks for you help Joe, that will have to do.