Counta for prior 10 rows

Hi all... having a little trouble getting a rolling range into a formula, and am trying to prevent adding another loop to my code if there's a quicker and easier way to achieve the same.

All I really want to say is "if there are any values in any cell in the prior 10" do some stuff.

The column is 2 to the right of the active cell.

Please Login or Register  to view this content.

Error is: 1004, Method Range of object _Global failed.

Thanks for any help!

Any idea what I'm doing wrong?