counting records

[Panagiotis Marantos]

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

[Norman Jones]

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" <[email protected]> wrote
in message news:[email protected]...
> 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

[Duncan]

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

[Duncan]

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" <[email protected]> wrote
> in message news:[email protected]...
> > 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

[NickHK]

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" <[email protected]> wrote
in message news:[email protected]...
> 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

[Norman Jones]

Hi Panagiotis,

However, why not simply:

countrecords = Application.CountA(Range("A1:A500"))


---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote
> in message news:[email protected]...
>> 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
>
>