0

I have names "ANDREW", "BLACK", "JONES", "JUNIPER", "NEWSHAM" and "SMITH" in the first vertical column of an Excel sheet. I wish to be able to enter a value in a separate excel cell and as I do so see the sheet immediately position itself to the next (alphabetically speaking) value in the first column. So if I key "J", then my sheet immediately positions itself with the topmost row starting "JONES". If I then follow the J with a "P" or for that matter a "U" it positions itself to "JUNIPER" being the next item alphabetically following my entry. The names are of course entered in alphabetic order. Your help please. I am happy with VBA programming.

Here is my code:

My alphabetic entries are located between A1 and A433.

I type a search value in cell L1, which triggers a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$L$1" Then
        Call MyMacro
    End If
End Sub

In addition, cell (1,9) has formula MATCH(L1,A2:A433)+1

When the subroutine MyMacro is executed:

Sub MyMacro()
ActiveWindow.ScrollRow = Worksheets(1).Cells(1, 9)
End Sub

it picks up the numerical MATCH value in cell 1,9 and positions to that row. This works sometimes but the entries in column A are a mixture of upper and lower case, and when entering values near the start of the table a Run Time Error - Type Mismatch appears. The cell 1,9 appears as "#N/A"

Ali Wood
  • 19
  • 2
  • Please share the input, the expected output and your current tentative solution your are trying to resolve your problem. – David Leal Sep 25 '22 at 13:05
  • If I knew how to attach my excel sheet I would. – Ali Wood Sep 25 '22 at 13:13
  • you can attach a screenshot or your can use a table markdown for that or to show the tables as a source code. When you edit your question, you have the image icon for inserting an image in the horizontal tool bar, as well as other feature for formatting your question. Check the help (question mark icon on the right). [Here](https://stackoverflow.com/help/formatting) you can find some help for formatting your question. – David Leal Sep 25 '22 at 13:39
  • I would recommend also to check: [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) and the following one: [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). All this will help you to get more attention to your question from the community. – David Leal Sep 25 '22 at 13:47
  • The search engine in Stack Overflow is not good enough, what I works for me is to do a search in Google for my problem adding `stackoverflow` and `excel` key words as part of the search. Maybe your question was already answered, and this is a sometime a more efficient way to find it. – David Leal Sep 25 '22 at 13:51
  • Has anyone any response please? – Ali Wood Sep 26 '22 at 11:20
  • The Change event only fires when you've finished typing. You'll need some kind of keypress event to capture each letter you type. This sounds like it does that: [Is there any event that fires when keys are pressed when editing a cell?](https://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell). Then use [FIND](https://excelmacromastery.com/excel-vba-find/) to scroll to the correct row. – Darren Bartrup-Cook Sep 27 '22 at 14:19
  • Thanks very much. Shame there's nothing simpler, but I'll give it a try. – Ali Wood Sep 28 '22 at 17:46

0 Answers0