PDA

View Full Version : Find och go to cell



tiso
04-28-2006, 03:37 AM
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:dunno

Bob Phillips
04-28-2006, 04:38 AM
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.



'-----------------------------------------------------------------
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


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

tiso
05-02-2006, 03:45 AM
Thank you, that seems to work :yes