Passing a Range to a Subroutine using CELLS isn't working

[Vaslo]

I have the following code to run a sub:

Please Login or Register  to view this content.

which I want to replace with

Please Login or Register  to view this content.

to make it dynamic.

When I try to run it, I get the following error

Run-Time Error '1004': Method 'Range' of object '_Worksheet' failed.

Not sure what I am doing wrong as it works with:

Please Login or Register  to view this content.

Thank you.

[Norie]

In the first 2 lines of code you don't have a worksheet reference for Cells, kind of surprised the first one worked - wsCalc must have been the active sheet when you ran it.

Try this.

Please Login or Register  to view this content.

Or this.

Please Login or Register  to view this content.

[leelnich]

What's the value of lCol at time of error?

[Vaslo]

In the first 2 lines of code you don't have a worksheet reference for Cells, kind of surprised the first one worked - wsCalc must have been the active sheet when you ran it.

Try this.

Please Login or Register  to view this content.

Or this.

Please Login or Register  to view this content.

Adding that extra reference to wsCalc made the difference. Hardest thing for a VBA newbie is understanding when you can (and should) reference up the chain of the object model and when you don't need to.

Thanks for your help.

[Norie]

You should fully reference things all the time.