object var or with block var not set

**vba_php** · 08-06-2020, 01:52 AM

can someone give me a little push here? I've tried all the breaks I can with this code and checked vars that have values in the immediate window and I can't seem to get why the worksheet object is not being set and transacted. It's been a while since I've written legacy VB like this, so I'm sure it's probably something very simple. Here is my code:

Please Login or Register  to view this content.

the code fails on the first line of the loop:

Please Login or Register  to view this content.

every var returns a val in the immediate window if I break it on the error line except the worksheet object. Can someone spot my misstep? thank you so much.

Adam

**Andy Pope** · 08-06-2020, 02:17 AM

I have added a Case Else which will report unknown states. As well as a test around the For Each loop to make sure ws is a valid reference.

It could be that the users are following the instructions provided, which is to enter a state in LOWERCASE.
As you then go on and compare that information to UPPERCASE states, which of course will fail to match any.

Please Login or Register  to view this content.

**jindon** · 08-06-2020, 02:23 AM

s1 is lower case whereas select case uses upper where it is case sensitive.
Try change to

Please Login or Register  to view this content.

**huuthang_bd** · 08-06-2020, 02:24 AM

Try add Option Compare Text at beginning of the module and add the red lines

Please Login or Register  to view this content.

**vba_php** · 08-06-2020, 02:25 AM

ha ha ha. thank you for spotting my nonsense Andy. =) I only need a part of your addition. this fixed it just fine:

Please Login or Register  to view this content.

I haven't written VB in a long time, but before that I wrote it for 20 years. Can't believe I overlooked such simplicity! Oh, and by the way, this is just being produced. no users are actually using it yet. which makes it even worse. I couldn't spot the mistake during my own testing phase! thanks again. have a good day.

**vba_php** · 08-06-2020, 02:26 AM

and thanks to the others who also replied.

**mjr veverka** · 08-06-2020, 02:56 AM

?

Please Login or Register  to view this content.

**vba_php** · 08-06-2020, 03:33 PM

porucha,

thank you for your contribution. I just saw it. I always know that my coding routines are not the most efficient possible. I don't give much thought into making them so. Your routine is obviously much better than mine. Appreciate it. If I ever do change it, I will consolidate it in that way.

object var or with block var not set

LinkBack

Thread Tools

Rate This Thread

Display

object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Re: object var or with block var not set

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Object or With Block Error

macro block object

[SOLVED] Object Variable or with block not set

[SOLVED] Object Variable/With Block Not Set When Naming Ranges

[SOLVED] Object Variable Or With Block Not Set

Error #91 Object Variable or With Block Not Set

object variable or with block not set

Object variable or with block not set

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions