I have a column of data - example below. The content of this column stays the
same each day but the amount of each value changes. Therefore tomorrow, there
could be 3 cells containing apple, and 10 containing banana.
I want to write something, whereby I can write in banana and the cursor will
go to the last cell in which the word banana (or any of the other fruit) is
located. Any ideas??

apple
apple
banana
banana
banana
orange
orange

Edit Find and enter banana and select Find All

Then select the last entry on the presented list.
--
Gary's Student


"Sarah" wrote:

I have a column of data - example below. The content of this column stays the
same each day but the amount of each value changes. Therefore tomorrow, there
could be 3 cells containing apple, and 10 containing banana.
I want to write something, whereby I can write in banana and the cursor will
go to the last cell in which the word banana (or any of the other fruit) is
located. Any ideas??

apple
apple
banana
banana
banana
orange
orange

Hi
Thanks but not quite what I meant.
I need to automate it. i.e. I want to type banana in a specific cell and
then the cursor to automatically move to the last cell in which banana
appears.

"Gary''s Student" wrote:

Edit Find and enter banana and select Find All

Then select the last entry on the presented list.
--
Gary's Student


"Sarah" wrote:

I have a column of data - example below. The content of this column stays the
same each day but the amount of each value changes. Therefore tomorrow, there
could be 3 cells containing apple, and 10 containing banana.
I want to write something, whereby I can write in banana and the cursor will
go to the last cell in which the word banana (or any of the other fruit) is
located. Any ideas??

apple
apple
banana
banana
banana
orange
orange

Hi Sarah

The following array formula will give you the address of the relevant
cell.
With fruits entered in D1:D8 and fruit required in F1

{=ADDRESS(MAX((D1:D8=F1)*ROW(D1:D8)),4)}

To commit or edit an array formula use Control + Shift + Enter (CSE)
instead of just Enter.
When you use CSE, Excel will include the curly braces { } . Do not
type them yourself.

--
Regards

Roger Govier


"Sarah" wrote in message
...
Hi
Thanks but not quite what I meant.
I need to automate it. i.e. I want to type banana in a specific cell
and
then the cursor to automatically move to the last cell in which banana
appears.

"Gary''s Student" wrote:

Edit Find and enter banana and select Find All

Then select the last entry on the presented list.
--
Gary's Student


"Sarah" wrote:

I have a column of data - example below. The content of this column
stays the
same each day but the amount of each value changes. Therefore
tomorrow, there
could be 3 cells containing apple, and 10 containing banana.
I want to write something, whereby I can write in banana and the
cursor will
go to the last cell in which the word banana (or any of the other
fruit) is
located. Any ideas??

apple
apple
banana
banana
banana
orange
orange

This is very automatic. Assume that the list is in column A. Type an entry
into cell B1 and the correct cell in column A will automatically be selected.
Enter the following code in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1")) Is Nothing Then
Exit Sub
End If
n1 = Range("A65536").End(xlUp).Row
v = Range("B1").Value
For i = n1 To 1 Step -1
If Cells(i, "A").Value = v Then
Cells(i, "A").Select
Exit Sub
End If
Next
End Sub

If you are not familiar with VBA, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


REMEMBER: worksheet code
--
Gary's Student


"Sarah" wrote:

Hi
Thanks but not quite what I meant.
I need to automate it. i.e. I want to type banana in a specific cell and
then the cursor to automatically move to the last cell in which banana
appears.

"Gary''s Student" wrote:

Edit Find and enter banana and select Find All

Then select the last entry on the presented list.
--
Gary's Student


"Sarah" wrote:

I have a column of data - example below. The content of this column stays the
same each day but the amount of each value changes. Therefore tomorrow, there
could be 3 cells containing apple, and 10 containing banana.
I want to write something, whereby I can write in banana and the cursor will
go to the last cell in which the word banana (or any of the other fruit) is
located. Any ideas??

apple
apple
banana
banana
banana
orange
orange