VBA run-time error

[jbjohansen]

Hello,

I have the following VBA code in an excel worksheet. It worked fine until recently then it started throwing a run-time error. If I comment out the 4th line of code it doesn't error out but of course it also doesn't work.
The error it throws is:

Microsoft Visual Basic
Run-time error ‘2147417846 (80010108)’:
Method ‘Value2’ of object ‘Range’ Failed

Any idea how to make it work and why it stopped working? I'm using Office 2010. Thanks in advance, John

Please Login or Register  to view this content.

[BadlySpelledBuoy]

Hi,

I suspect this is error is being caused by something else in your workbook as I get no error with your code in a test workbook.
One thing I will say is, the mere fact the code runs and changes the contents of the target cell to Proper case fires the sub again, and again and again.. That could result in an error when Excel decides enough is enough.

Try this slight rewrite and see if you still get the error.

Please Login or Register  to view this content.

BSB

[jbjohansen]

Hi,
That seems to have done the trick, how do I mark it solved?
Thank you,
John

[BadlySpelledBuoy]

Glad I could help

To mark the thread as solved, select Thread Tools from the menu link above and mark this thread as SOLVED.

BSB