I am using the following code to count the no of entries in colA i
sheet2
I am currently in sheet1

Sub check()
Set myrange = Sheets("sheet2").Range(Cells(1, 1), Cells(4000, 1))
MsgBox Application.CountA(myrange)
End Sub

This gives an error as follows

"run time error 1004

application or object defined error"

but when I am in sheet2 and run this macro there is no error

how to avoid this

--
chandran1
-----------------------------------------------------------------------
chandran19's Profile: http://www.excelforum.com/member.php...fo&userid=1449
View this thread: http://www.excelforum.com/showthread.php?threadid=26121

Hi chandran19,

something like:
Sub check()
Dim myRange As Range
Sheets("sheet2").Activate
Set myRange = Range(Cells(1, 1), Cells(4000, 1))
MsgBox Application.CountA(myRange)
End Sub

Best regards

Wolf


-----Original Message-----

I am using the following code to count the no of entries
in colA in
sheet2
I am currently in sheet1

Sub check()
Set myrange = Sheets("sheet2").Range(Cells(1, 1), Cells
(4000, 1))
MsgBox Application.CountA(myrange)
End Sub

This gives an error as follows

"run time error 1004

application or object defined error"

but when I am in sheet2 and run this macro there is no
error

how to avoid this?


--
chandran19
---------------------------------------------------------
---------------
chandran19's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=14497
View this thread:
http://www.excelforum.com/showthread...hreadid=261217

.

Hi

Use it like this

In your example "cells" in the code will use the cells on the activesheet
See the dot before it in my example

Sub check()
Dim myrange As Range
With Sheets("sheet2")
Set myrange = .Range(.Cells(1, 1), .Cells(4000, 1))
End With
MsgBox Application.CountA(myrange)
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"chandran19" wrote in message ...

I am using the following code to count the no of entries in colA in
sheet2
I am currently in sheet1

Sub check()
Set myrange = Sheets("sheet2").Range(Cells(1, 1), Cells(4000, 1))
MsgBox Application.CountA(myrange)
End Sub

This gives an error as follows

"run time error 1004

application or object defined error"

but when I am in sheet2 and run this macro there is no error

how to avoid this?


--
chandran19
------------------------------------------------------------------------
chandran19's Profile: http://www.excelforum.com/member.php...o&userid=14497
View this thread: http://www.excelforum.com/showthread...hreadid=261217