Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
can somebody provide me with an answer as to why the following function
returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
Hi Panagiotis,
Try: '============= Public Function countrecords() As Integer Dim count As Integer Dim cell As Range count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If Not IsEmpty(cell.Value) Then count = count + 1 End If Next cell countrecords = count End Function '<<============= --- Regards, Norman "Panagiotis Marantos" wrote in message ... can somebody provide me with an answer as to why the following function returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
The error is with "Is Not Null"
try If cell.Value < "" Then Duncan Panagiotis Marantos wrote: can somebody provide me with an answer as to why the following function returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
Panagiotis,
The keyword "Is" is used with Objects. Dim WB As Workbook On Error Resume Next Set WB=Workbooks.Open(PathToFile) If Not WB Is Nothing Then..... But cell.value is not an object. Depending what you are test for: cell.value<0 or Not (cell.value=0) cell.value<"" cell.value<Empty NickHK "Panagiotis Marantos" wrote in message ... can somebody provide me with an answer as to why the following function returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
or......
Function countrecords() As Integer Dim count As Integer count = 0 For Each cell In Worksheets("sheet1").Range("A1:A500") If cell.Value < isblank = True Then count = count + 1 End If Next cell countrecords = count MsgBox count End Function Norman Jones wrote: Hi Panagiotis, Try: '============= Public Function countrecords() As Integer Dim count As Integer Dim cell As Range count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If Not IsEmpty(cell.Value) Then count = count + 1 End If Next cell countrecords = count End Function '<<============= --- Regards, Norman "Panagiotis Marantos" wrote in message ... can somebody provide me with an answer as to why the following function returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting records
Hi Panagiotis,
However, why not simply: countrecords = Application.CountA(Range("A1:A500")) --- Regards, Norman "Norman Jones" wrote in message ... Hi Panagiotis, Try: '============= Public Function countrecords() As Integer Dim count As Integer Dim cell As Range count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If Not IsEmpty(cell.Value) Then count = count + 1 End If Next cell countrecords = count End Function '<<============= --- Regards, Norman "Panagiotis Marantos" wrote in message ... can somebody provide me with an answer as to why the following function returns an error on the if line? the error message is "object required" Function countrecords() as integer Dim count As Integer count = 0 For Each cell In Worksheets("Saved Records").Range("A1:A500") If cell.Value Is Not Null Then count = count + 1 End If Next cell countrecords = count End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Records Between Two Dates | Excel Worksheet Functions | |||
Counting records f(x) two columns | Excel Discussion (Misc queries) | |||
counting records | Excel Worksheet Functions | |||
Counting unique records | Excel Worksheet Functions | |||
Counting records | Excel Programming |