Object variable not set

**jasondu** · 06-11-2012, 11:10 AM

Hey all,

the following piece of code keeps giving me Object variable not set error (below bolded).. and I am not sure why.. if anyone can fix it up / tell me whats wrong I would really appreciate it!

Please Login or Register  to view this content.

**shg** · 06-11-2012, 11:16 AM

You can't combine those tests in a single statement -- if the range is Nothing, testing the row gives a runtime errror.

**jasondu** · 06-11-2012, 11:25 AM

Hi thanks for the reply shg,

So you're saying that if the range is nothing, it can't execute the second part of the test? that it will give a runtime error?

Please Login or Register  to view this content.

How can I test for both "non nothing" range as well as a specific value for the same cell?

**StevenM** · 06-11-2012, 11:27 AM

I'll make two observations (neither witch directly answer your question).

(1) How are you using "year"? What is it? It is also a VBA function. If you are trying to use it as a function, you are using it wrong. And if you are trying to use it as a variable, you shouldn't.

(2) As for the line:

Please Login or Register  to view this content.

There is no need for Val(). And is "Cells" supposed to reference the Active worksheet? Or "With unionrange"? If it is supposed to reference unionrange then you need a dot (period) before "Cells".

If none of this helps, I apologize.

**JosephP** · 06-11-2012, 11:29 AM

per shg's comment you need two tests

Please Login or Register  to view this content.

**StevenM** · 06-11-2012, 11:30 AM

I believe what Shg is suggesting is:

Please Login or Register  to view this content.

**jasondu** · 06-11-2012, 11:35 AM

Thanks stephen ..

definitely your advice helps - I'm learning every step of the way

1.) You're right I should rename "year" as it is being used as a variable .. didn't think of the VBA function at the time of naming
2.) I removed Val() and added a dot in front of cells as it is part of "With unionrange" thanks!

however I still get an error with object variable or with block column not set error as shg pointed out

**StevenM** · 06-11-2012, 11:41 AM

Can you upload a workbook?

P.S. You should have "Option Explicit" at the top of each of your modules. That would help you catch the "year" problem.

Also, in the VBE (Visual Basic Editor), go to Tools -> Options and click the "Editor" tab and check the box next to "Require Variable Declarations" (if not checked). (This is not necessary, but good practice. Only needs to be done once.) Then any new modules will come with "Option Explicit" at the top of each modules. But you will still need to add it to all the old modules. Doing this helps catch all sorts of mistakes.

**jasondu** · 06-11-2012, 12:03 PM

Actually this did the trick! Works perfectly now ...

Thanks guys for the repeated help over multiple threads .. I think its great that there are people willing to go out of their way to help beginners like me

Cheers and have a great day

Originally Posted by shg

You can't combine those tests in a single statement -- if the range is Nothing, testing the row gives a runtime errror.

Originally Posted by JosephP

per shg's comment you need two tests

Please Login or Register  to view this content.

Originally Posted by StevenM

I believe what Shg is suggesting is:

Please Login or Register  to view this content.

**jasondu** · 06-11-2012, 12:06 PM

and yeah stevenM, I did have option explicit on .. just this code wasn't the only code in the module so thats why I didn't copy option explicit .. and I just found out if I hover over the code in break mode - it will actually show me where my problem is (.. eventually)