Jumping Cursor without having to scroll

[robocop]

Hello Excel Universe,

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

For example: If 4/30/17 is entered in A1, 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

<tbody>
</tbody>

 

[My Aswer Is This]

Try this:
This only works when you enter new data into column "A" as shown
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim c As Range
Dim Lastcolumn As Long
Lastcolumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
 
    For Each c In Range(Cells(Target.Row, 2), Cells(Target.Row, Lastcolumn))
        If c.Value = Target.Value Then c.Select: Exit For
    Next
End If
End Sub

 

[tonyyy]

A similar but slightly different approach...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Dim LastColumn As Long, d8 As Range
    LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
    Set d8 = ActiveSheet.Range(Cells(1, 1), Cells(1, LastColumn)).Find(What:=Range("A1").Value, After:=Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    d8.Select
    ActiveWindow.ScrollColumn = d8.Column
End If
End Sub

Uses a Range.Find method rather than a For/Next loop - so should be at least 1/10,000 of a millisecond faster! - and sets a .ScrollColumn property in order to "jump" to the column.

Cheers,

tonyyy

 

[robocop]

Tonyyy!!! Thanks a lot man, this code works perfect!! You're a life saver. Very much appreciated.

 

[tonyyy]

You're welcome, robocop. Glad it worked out...