Why dynamic range doesnt work with COUNTA

[Kushal_1991]

WorksheetFunction.CountA(Worksheets("Data_Rough").Range(Range("C4"), Range("C4").End(xlDown))) ................it gives application defined/object defined error. Can anybody explain?

[Andy Pope]

Try fully qualifying all the range references

Please Login or Register  to view this content.

[Kushal_1991]

Actually what should I do if I write a code in the sheet rather than this workbook or modules and want to select a dynamic range from another worksheet?

[Andy Pope]

when you say 'write code in sheet' do you mean the Sheet object or acutally in a cell as a formula?

[Kushal_1991]

Sheet Object

[Andy Pope]

Apart from the result not actually being stored anywhere that code should work.

Perhaps you should post workbook example detailing what you have used and what exactly you are trying to do

[Kushal_1991]

Picture1.jpg
Here, I want to count the number of non blank cells within a range (which i want to select dynamically) in worksheet Data_Rough when the code is written in worksheet Data

[Andy Pope]

that's a picture, I asked for a "workbook example"

and you have not explained how that code should be used? where should the result of the COUNTA go?

[Kushal_1991]

I just want the answer in a MSGBOX

[Andy Pope]

Please Login or Register  to view this content.

I'm still non the wiser about how this should actually work but above will display information in msgbox