You can use a formula like this (when j = 17):
=SUMPRODUCT(--(L1:L17=L17))

In code, you can do something like:

Dim myRng As Range
Dim myCell As Range
Dim j As Long
Dim myCount As Long

j = 17
With ActiveSheet
Set myRng = .Range("L1:L" & j)
Set myCell = .Range("l" & j)
myCount = .Evaluate("sumproduct(--(" & myRng.Address _
& "=" & myCell.Address & "))")

End With

MsgBox myCount


Did you really mean to include the row with the value?

geebee wrote:

hi,

i have the following:
If Application.WorksheetFunction.CountIf(Range("L1:L" & j), Range("L" &
j).Text) 1 And Range("L" & j) < "" Then

this works in other sheets, but i did some comparison to te other sheets and
found out that the other sheets do not contain values greater than 200
characters in the column L. How can i amend or fix this?

thanks in advance,
geebee

--

Dave Peterson