Selecting a range within a sorted range (Applescript)

[Possum Stu]

I'm having a problem working with ranges.

Suppose you have a column of 15 cells, range A1:A15, where 9 have cell
value "apple", 4 have value "plum", and 2 have value "peach", all mixed
up. You don't know how many pieces of fruit you have -- it could be 10,
it could be 20, it just happens to be 15 now. You don't know what kinds
of fruit you have -- last week we had bananas, next week it could be
cherries. And you don't know how they're distributed (plum, apple,
apple, plum, etc.). All you know for certain is that you have a column
of fruit.

My method of finding out the overall size of a range is to select the
first cell, select the "active region" of that cell, then count the
rows and use that number as the outer boundary of the range. Now I have
a fruitRange.

I want to describe the range of cells with the value "plum" within my
sorted range. What could work is if I first sort the range, then use a
loop to "find" each instance of a plum and record the first instance's
cell location and the last instance's cell location before the find
function returns to the top again, and use that.

This seems awfully inefficient and convoluted to me. Perhaps there is a
more powerful way to work with ranges that I'm overlooking?

In the alternative, is there a way to create an Applescript list that
is made up only of the unique values within the fruitRange? Maybe
that's the way to go.

TIA for your help.

 

[Bernard Rey]

Basically, the way to do this really easily is the "COUNTIF" function. Let's
say you have the string "plum" in cell B1. Just type :

=COUNTIF(A:A;B1)

and you're done, you won't need to sort or count anything else.

Couldn't tell if this is easily doable with AppleScript. I guess you can
call the "COUNTIF" function through it too. But you can easily do it with a
VB macro :

Sub CountIt()
Dim MyFruit As String
MyFruit = "plum"
' or Range("B1").Value
test = Application.WorksheetFunction.CountIf(Range("A:A"), MyFruit)
[C1] = test
End Sub

HTH...