Re: Go to what I just typed

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It just put the number of text entries in ColA in the formula's cell

I decided to take a different tack that suits my needs:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lrow As Long
On Error GoTo ws_exit
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Count > 1 Or Target = "" Then GoTo ws_exit
If Intersect(Target, Range("A17:A" & Lrow)) Is Nothing Then GoTo ws_exit
Application.EnableEvents = False
Range("I17:I" & Lrow).FillDown
Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
ws_exit:
Application.EnableEvents = True
End Sub

It provides some more flexibility like allowing me to insert a row where
I want and even make some entries in other columns before triggering the
sort by putting the name in ColA.

---
David

Dave Peterson wrote

> What happened when you tried the formula?
>
> Maybe your fathoming isn't correct.
>
> David wrote:
>>
>> I can't fathom how any of these suggestions would accomplish what I
>> want to happen.
>> 1. Names are entered in the range A17 to the bottom of the table
>> 2. I navigate to the bottom of the table and insert a row (I could
>> insert a new row anywhere, but the sort would put it at the bottom
>> anyway, so why not just insert it there in the first place)
>> 3. I type a new name in the new A cell. For testing I just typed AAA,
>> AAA 4. Worksheet_calculate fires and correctly puts AAA, AAA and
>> empty cells to right at the top of the table
>> 5. I now have to scroll to the top (or anywhere a new entry might
>> appear alphabetically) to make entries relevant to AAA, AAA in B, C,
>> D, etc.
>>
>> I need additional code that would get me there without that
>> scrolling.
>>
>> Something like Set Found = Range("A:A").Find (What:= <whatever new
>> name I typed>)
>> Or
>> Application.GoTo Range(<whatever new name I typed>), True
>>
>> --
>> David
>>
>> Dave Peterson wrote
>>
>> > You could use worksheet_change to fire the worksheet_calculate.
>> > But maybe even simpler would be to put a helper formula in a cell:
>> >
>> > =counta(a:a)
>> >
>> > As soon as you type something in Column A, this formula will
>> > recalculate and then your worksheet_calculate event will fire.
>> >
>> > You could even put it at the top of the work*** (row 1) to keep
>> > track of how many rows of data you have
>> >
>> > or a variation:
>> > =counta(a:a)-2
>> > (if you had two header rows)
>> >
>> > ps. don't put the formula in column A -- or limit the range:
>> > =counta(a2:A65536)
>> >
>> >
>> >
>> > David wrote:
>> >>
>> >> I stumbled onto the Worksheet_Calculate() method of sorting and
>> >> find it very useful in one of my workbooks.
>> >>
>> >> Private Sub Worksheet_Calculate()
>> >> Dim Lrow As Long
>> >> On Error GoTo ws_exit:
>> >> Application.EnableEvents = False
>> >> Lrow = Cells(Rows.Count, "A").End(xlUp).Row
>> >> Range("A17:H" & Lrow).Sort key1:=Range("A17"), header:=xlNo
>> >> Range("I17:I" & Lrow).FillDown 'insure Formula gets added where
>> >> needed ws_exit:
>> >> Application.EnableEvents = True
>> >> End Sub
>> >>
>> >> Routinely I will insert a row at the bottom of existing data for
>> >> new data and then type in a name
>> >>
>> >> I would like to add something to the routine so when I type in
>> >> that name, it not only immediately gets put in the proper row
>> >> alphabetically, but the routine would then jump to that row. I
>> >> can't figure it out.
>> >>
>> >> --
>> >> David
>> >
>



--
David
.

Quantcast