Jumping Cursor without having to scroll

robocop

New Member
Joined
Jan 10, 2015
Messages
12
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.

ABCDAH
14/30/171/13/171/14/171/15/174/30/17
2$200$400$500$600
3$400$500$600$700
4$10$20$30$50
5$15$25$40$55

<tbody>
</tbody>
 

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.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
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
 
Last edited:

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
A similar but slightly different approach...

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

New Member
Joined
Jan 10, 2015
Messages
12
Tonyyy!!! Thanks a lot man, this code works perfect!! You're a life saver. Very much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,312
Members
414,052
Latest member
Dual Showman

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