excel/vba - find fist and last occurrence of a particular value in a column

Question

So if I have a column such as:

    A1
1   Apple
2   Apple
3   Apple
4   Oj
5   Oj
6   Oj
7   Oj
8   Pear
9   Pear

How could I return the values 1 & 3 for Apple, 4 & 7 for OJ, etc?

Solution

Formula-wise you can use MATCH functions, e.g. for first Apple position

=MATCH("Apple",A1:A9,0)

for last

=MATCH(2,INDEX(1/(A1:A9="Apple"),0))

or if the fruit are sorted as per your example (or merely grouped) you can get the last by adding the number of apples to the first -1

so with first MATCH function in C1 that would be

=COUNTIF(A1:A9,"Apple")+C1-1