SCROLL ON CELL CHANGE

[sunilpatel]

hi from a newbie

in column A i have a very long list of medical products in alphabetical
order.
I keep having to scroll up and down all day to find items. I would like a
sheet macro to detect a cell change e.g in B1 i enter "para" and column A
scrolls down until "Paracetamol" is visible on the screen in column A. (i.e
column A automatically scroll to line 400 where cell A400 is Paracetamol).

Thanks in advance

Sunil

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this
Now, if you type para in cell a1 you will go there

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Call gotoltr
End Sub
Sub gotoltr()
lr = Cells(Rows.Count, 1).End(xlUp).Row
x = UCase(Range("a1"))
ml = Len(x)
On Error Resume Next
For Each c In Range("a4:a" & lr)
If Left(Trim(UCase(c.Value)), ml) = x Then
Exit For
End If
Next
If UCase(Range("b1")) = "Y" Then Cells(c.Row, 1).Select
End Sub

 

[Vergel Adriano]

Freeze the window pane in cell B2 by selecting cell B2, then, in main menu,
select Window->Freeze Panes.

Then in the worksheet code module, try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Dim c As Range
Set c = Me.Range("A:A").Find(Target.Value)
If Not c Is Nothing Then
ActiveWindow.ScrollRow = c.Row
End If
End If
End Sub

 

[Joerg Mochikun]

Following macro is different from what you want ("change in B1") but more
flexible. It assumes that you've put the cursor into the column of the data
table that you want to search (so it works on any column). It pops up an
inputbox where you would input "para". Upon Enter the screen scrolls to the
first cells which starts with "para" (the normal Excel Find command would
find para anywhere in the cell - that's not what you want) .The search is
not case sensitive, so it will find Paracetamol, paracetamol or PARAcetamol.

I recommend that you assign a keyboard shortcut to this macro: In the Excel
menu go to Tools->Macro->Macros->[select macro]->Options.

Cheers,
Joerg Mochikun

Sub QuickSearch()
Dim SearchString As String
SearchString = InputBox("Search for...")
For Each cell In
Range(ActiveCell.CurrentRegion.Columns(ActiveCell.Column).Address)
If LCase(SearchString) = Left(LCase(CStr(cell.Text)),
Len(SearchString)) Then
cell.Select
Exit Sub
End If
Next cell
End Sub