Macro error - 1004 Happens Randomly - Using Evaluate

Hello,

I have a macro that is run by multiple people.The macro runs perfectly fine for me, however, sometimes the other people running the macro get a 1004 error stating "Unable to get the evaluate property of the Worksheet class". I am stumped what is causing this. It is taking a column of names... some in all CAPS, some in all lower case, some in proper case and making them all proper case....

The section of code that sometimes gives the error is:

Please Login or Register  to view this content.

I can post the rest of the code if nessecarry, however, this is the only line that ever has an issue.

Try this:

Please Login or Register  to view this content.

Clamping the range reduces the extents, getting the reference style accommodates people who use R1C1 references, and getting an external address serves in lieu of r.Parent.Evaluate (or r.Worksheet.Evaluate).