-
Find och go to cell
I have a worksheet with about 200 columns of data. The first cell in every column contains a different name (all names are in the same row).
In order to avoid a lot of horizontal scrolling, I would like to be able to type a name in a messagebox, and then directly jump to the cell containing that name. An even better solution I guess would be to create a listbox with all the names, and then by choosing one of the names in the listbox, the cursor moves to the cell/column containing that name. I should add that all the cells containing names are locked.
Can anyone help me
-
This will create a set of range names with those values, so you can goto them by selecting them from Names box to the left of the formula bar.
[vba]
'-----------------------------------------------------------------
Private Sub Workbook_Open()
'-----------------------------------------------------------------
Dim i As Long
With Worksheets("Sheet1")
On Error Resume Next
For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
ThisWorkbook.Names.Add Name:=.Cells(1, i).Value, _
RefersTo:="=" & .Cells(1, i).Address(, , , , True)
Next i
End With
End Sub
[/vba]
This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code
-
Thank you, that seems to work
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules