Go to what I just typed

[David]

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

[Dave Peterson]

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 worksheet (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

--

Dave Peterson

[David]

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 worksheet (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
>

[Dave Peterson]

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 worksheet (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
> >

--

Dave Peterson

[David]

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 worksheet (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