Problem with variables misbehaving!

[crocodile]

This is my first post on the forum – so hello to you all and many thanks for reading this.

I am by no means new to Excel/VBA – but have come up against an issue which is making me feel like a real newbie! I sincerely hope someone can tell me where I’m going wrong.

I have an issue with an error check – the easiest way I can explain is to show you the code which DOES work and the code which I’d LIKE to work (but doesn’t!):

For information:

• H18 is an input field populated by a dropdown list.
• E30 contains a vlookup the result of which is either a name, tbc or nothing.

This code works:

Dim NewProjectStatus As String

'Set the variables from the Manage Project form[INDENT]NewProjectStatus = Sheets("Manage Project").Range("H18").Value[/INDENT]
 
'Check to make sure that the a resource has been assigned if the Status is being changed to "In Flight"[INDENT]If NewProjectStatus = "In Flight" And (Sheets("Manage Project").Range("E30").Value = Empty Or Sheets("Manage Project").Range("E30").Value = "tbc") Then
[/INDENT]
[INDENT=2]MsgBox "You cannot set a project to In Flight unless there is an Assigned Resource", vbExclamation
Sheets("Manage Project").Range("H18").ClearContents
End[/INDENT]
[INDENT]End If[/INDENT]

But if I change both values to variables, as follows, it doesn’t work:

Dim NewProjectStatus As String
[B]Dim AssignedResource As String[/B]
 
'Set the variables from the Manage Project form[INDENT]NewProjectStatus = Sheets("Manage Project").Range("H18").Value
[B]AssignedResource = Sheets("Manage Project").Range("E30").Value[/B][/INDENT]
 
'Check to make sure that the a resource has been assigned if the Status is being changed to "In Flight"[INDENT]If NewProjectStatus = "In Flight" And ([B]AssignedResource [/B]= Empty Or [B]AssignedResource [/B]= "tbc") Then
[/INDENT]
[INDENT=2]MsgBox "You cannot set a project to In Flight unless there is an Assigned Resource", vbExclamation
Sheets("Manage Project").Range("H18").ClearContents
End[/INDENT]
[INDENT]End If[/INDENT]

If E30 is empty the code just doesn’t trap the error.
If E30 is “tbc” it works fine.

Why does this work with E30 defined explicitly, but not if I set it as a variable? I'm clearly missing something - I just don't know what!!

 

[crocodile]

Brilliant!!

Using "0" both error cases are trapped. Now I know the answer, I think I understand the problem too and I REALLY wanted to understand why this wasn't working!!!

No global variables declared - they're all within the procedures. Although, I should probably get in the habit of using Exit Sub - it's just me being lazy!

Huge thanks Fluff - I am incredibly grateful for your help.

 

[Fluff]

Glad to help & thanks for the feedback