Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if Range EXISTS | Excel Programming | |||
Check to see if a range name exists on a sheet | Excel Programming | |||
Check if a named range exists with VBA? | Excel Programming | |||
Check if named range exists! | Excel Programming | |||
Check if a number exists in a range? | Excel Discussion (Misc queries) |