Text search and "Go To"...

[Allan Grates]

I have a logistics Workbook with multiple worksheets. One worksheet is named:
Monthly Inventory. In column "D", I have the name of the product. At the top
of the page I have a section of frozen panes that I keep for instant display
for values. I'm looking for a solution to have a text box within my frozen
pane to type the name of the product into and have excel scroll it to show it
at the bottom of the frozen panes. I've searched the discussions and tried
several formulas, A NO GO on my part. Is it possible to make this happen?

 

[Otto Moehrbach]

Allan
A formula cannot scroll anything. The only way to get what you want is
with VBA programming. I suggest you use Data Validation in a cell in your
frozen area to select the product from a list. You select a product and the
following macro will fire automatically and do what you want. I assumed
your Data Validation cell is D4. Change this as needed. This macro is a
sheet event macro and must be placed in the sheet module of your sheet. To
access that module, right-click on the sheet tab and select View Code.
Paste this macro into that module. "X" out of the module to return to your
sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColD As Range, TheRow As Long
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("D4")) Is Nothing Then
Set rColD = Range("A9", Range("A" & Rows.Count).End(xlUp))
TheRow = rColD.Find(What:=Range("D4"), LookAt:=xlWhole).Row
With ActiveWindow
.ScrollRow = TheRow
.ScrollColumn = 1
End With
End If
End Sub