Macro to Move Selected Cell to Top of Screen

[snsd]

Hi:

I have a "no brainer" question for someone who knows what they're doing
- which I don't!!! Please see the attached file for details.

I have a list in alphabetical order with the first letter of each word
as a header in its own cell. eg. the letter "A" as the header for
words that start with "A".... Very similar to an address book where
you'd have the letter "A" followed by all the names starting with
"A"....

I recorded a macro for each letter of the alphabet so that when you
click on the letter, it searches for the letter header and selects that
cell. The macro is listed below for the letter "A".

As per the attached picture, the macro finds the cell perfectly, but
I'd like the cell to automatically be repositioned to the top of the
screen below row 3 (which I have frozen) so that I can see more of the
words listed instead of having to manually scroll down. I know that I
can record a macro that will scroll X number of lines up but when I add
new rows to my list, it obviously messes-up the desired effect.

Below is the macro I created.

What do I need to do to have the selected cell reposition itself at the
top of the viewable screen beneath the frozen row. Any help would be
greatly appreciated.

If it makes sense to scrap my current methodology and get the same
result differently, please let me know.

Thx,

Dave

Sub FindA()
'
' FindA Macro
' Macro recorded 10/27/2004 by 829980739
'

'
Cells.Find(What:="A", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
True).Activate
End Sub


+-------------------------------------------------------------------+
|Filename: MoveToTopOfScreen.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=2742 |
+-------------------------------------------------------------------+

 

[Celtic_Avenger]

Once you have detemined the cell that contains the data your looking
for, I found the following

Application.Goto Reference:=Worksheets("Sheet1").Range("B15"),
Scroll:=True,

The only problem with this is that it scrolls down and also to the
right.


I have used cell B15 as the target cell in the above script,

what I think then needs to be done is that if the data you want is in
cell B15, you need to tell the code to scroll to A15 not B15, then it
wont scroll to the right.

so the final code would be

Application.Goto Reference:=Worksheets("Sheet1").Range("A15"),
Scroll:=True


.....................................................



Thought about this for five mins.........

What you could do is............Copy the contents of the cells in the
middle of the screen to a new inserted hidden column in "A" possition
in the worksheet.

Then use your search to look for the data in that column not in the
column in the middle of the page, then use the GOTO reference command
above to scroll down to that line......your code will find data in a
hidden column. This way you could use

Application.Goto Reference:=Worksheets("Sheet1").Range("A15"),
Scroll:=True

to goto you desired line.


I think this might work.....I'm no professional....but I think it may
work....it worked on a test file I created.


Celtic_Avenger