View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Howard Howard is offline
external usenet poster
 
Posts: 536
Default Check a range to see if a value exists in it

What stupid little thing am I missing with this 'simple' snippet of test code.

Whe
I have the letter A in Range("D1").

Range("G1:G10")is a range named lData and contains A,B,A,D,A,B,C,A,blank,blank.
(The real range is about 100 rows in a column with perhaps 25 entries followed by 75 blanks, the entry amounts will vary widely but no blanks within the entries, only after the last entry)

When I run the my test code "MsgBox lCnt" returns 0 (zero). Should return 4.

Option Explicit
Sub WFCData()
Dim i As String
Dim lCnt As Integer
Dim lData As Range
i = Range("D1").Value
lCnt = Application.WorksheetFunction.Count("lData", i)
MsgBox lCnt
End Sub

Thanks.
Regards,
Howard