Check a range to see if a value exists in it

H

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
 
M

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")
 
C

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
 
H

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
 
H

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top