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 |
Check a range to see if a value exists in it
On Monday, September 24, 2012 9:14:06 AM UTC+3, Howard wrote:
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 Count worksheet function only counts numbers, try this instead: Application.WorksheetFunction.CountA("lData") |
Check a range to see if a value exists in it
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Check a range to see if a value exists in it
On Sunday, September 23, 2012 11:14:06 PM UTC-7, Howard wrote:
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 Hi Claus, That worked just perfect!! Thanks a lot. Regards, Howard |
Check a range to see if a value exists in it
On Sunday, September 23, 2012 11:14:06 PM UTC-7, Howard wrote:
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 Hi Claus, That worked perfectly!! Thanks a lot. Regards,Howard |
Check a range to see if a value exists in it
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 |
All times are GMT +1. The time now is 12:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com