UDF gives compile error: expected:expression

How about just a formula?

Row\Col	A	B	C
1	b	7	B1: {=IF(A1<>"b", "", MATCH(1, (A2:A27="b")+(A2:A27=""), 0) - 1)}
2	a
3	a
4	a
5	a
6	a
7	a
8	a
9	b	4
10	a
11	a
12	a
13	a
14	b	2
15	a
16	a
17	b	7
18	a
19	a
20	a
21	a
22	a
23	a
24	a
25	b	1
26	a

Hi adh2

Check the spelling

Puclic

Could you please explain how your formula works?

Watch the formula evaluate and post back if you still don't understand.

Excel doesn't see any dependency in your UDF on the rest of column A, so it won't automatically recalculate when a value changes. You can paper over it with Application.Volatile, but it's very bad design practice.

Set Rng = Range(Rng.offset(-1,-1), Rng.Offset(-15,-1))

You were passing variables as literal text.

Hi, just 3 points: (Besides the ones mentioned by others)

1. I'd recommend defining each variable in the function; for example variable c
Related, use a local variable to do the counting and assign that as the function's result at its end.
And use another variable to be defined as the range to be checked.

2. Your description says you want to count cells in the range antil one of them is not value "a".
As written, it will count any cell in the range that has value "a".

3. Your range offsets do not refer to the cells as described
The idea is that I enter "=counter(A1)" in cell B1, and the UDF then does this:
- Redefines rng to A2:A15
- Loop: checks is value in A2 "a". If it is; increase counter by 1, and check A3, A4, ..., until a value isn't "a"; then exit.

Here's my suggested revision . . .

Public Function Counter(Rng As Range) As Integer
  Application.Volatile   ' force the function to be recalculated when any cell is changed, not just its argument
  Dim Cell as Range  ' a cell to be tested
  Dim RangeToBeChecked as Range  ' the range of cells to be checked
  Dim theCount as integer

  Set RangeToBeChecked = Range(Rng.offset(1,0),Rng.Offset(14,0))   ' the 14 cells beneath the given argument

  theCount = 0

  For Each Cell In RangeToBeChecked
    If Cell.Value = "a" Then
      theCount = theCount + 1    ' count consecutive cells that do = "a"
    Else
      Exit For   ' quit counting when a cell does not = "a"
    End If
  Next

  counter = theCount   ' store final result for the function
End Function

Hope this helps.
- Stu

You're welcome.

When Boolean values appear in arithmetic expressions, True is coerced to 1 and False to 0, so False + False = 0, True + False = 1, and True + True = 2.