Check a range to see if a value exists in it

[Howard]

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

Where:
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

 

[Mohd]

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



Where:

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

Count worksheet function only counts numbers, try this instead:
Application.WorksheetFunction.CountA("lData")

 

[Claus Busch]

Hi Howard,

Am Sun, 23 Sep 2012 23:14:06 -0700 (PDT) schrieb Howard:

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

change to:
lCnt = Application.WorksheetFunction.CountIf(Range("lData"), i)


Regards
Claus Busch

 

[Howard]

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



Where:

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

Hi Claus,

That worked just perfect!!
Thanks a lot.

Regards,
Howard

 

[Howard]

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



Where:

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

Hi Claus,

That worked perfectly!!
Thanks a lot.

Regards,Howard

 

[Ben McClave]

Hi Howard,

I think there are two small changes. First, be sure to set "lData" = your data range. If it is a named range use:

Set lData = Sheet1.Range("ldata")

If it is not named, use:

Set lData = Sheet1.Range("G1:G10")

Then, change your function in the lCnt line to CountIf and drop the quotes:

lCnt = Application.WorksheetFunction.CountIf(lData, i)

Hope this helps

Ben