Why does range formula cause a type mismatch error?

Question

I have been working from a text book of VBA for Office 2000 but using Office 2010. When I put the following command in the intermediate window to update the value in the identified range in the VBA editor, I get a type mismatch error. I thought this command worked in Office 2000. Doesn't it work in 2010?

?Worksheets(1).Range("C10:D10").value = 5

Thank you

Answer 1

I found the problem. I was trying to run the command in the intermediate window. When I put it in a sub procedure, it ran fine.

Thanks.

Answer 2

Try

?WorksheetFunction.CountA (Range("C10:D10"))

This will return the number of non-blank entries in C10:D10.

To count the number of 5's in the range, use

?WorksheetFunction.CountIf(range("C10:D10"),5)

You can use your code only with a range containing a single cell, not multiple cells.

 Bad:

?Worksheets(1).Range("C10:D10").value = 5

Good

?Worksheets(1).Range("C10").value = 5

Answer 3

Hi,

Try this if you wish to count filled up cells in C10:D10

?Worksheets(1).application.worksheetfunction.counta(Range("C10:D10"))

Hope this helps.