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!!

 

[Fluff]

Hi & welcome to MrExcel.
Try changing Empty to ""

 

[crocodile]

Hi Fluff,

I've tried that and it behaves exactly the same.

I've tried, Empty, Null, Nothing, "" - everything I can think of - but I can't get it to work.

I've even tried splitting it into two separate clauses - one for "" and one for "tbc" - still no joy.

 

[Fluff]

What is the formula in E30?

 

[crocodile]

=IF($E$6="","",VLOOKUP($E$6,Projects!$B$2:$K$146,9,FALSE))

 

[Fluff]

That looks ok, Try adding this line

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

and run the code with E30 as ""
What does it say in the immediate window?

 

[crocodile]

OK - have done that and it says:

1 |0|

 

[Fluff]

In that case try

If NewProjectStatus = "In Flight" And (AssignedResource = [COLOR=#ff0000]0[/COLOR] Or AssignedResource = "tbc") Then

 

[crocodile]

Ahhhh - now, I'm one step ahead of you here!

Have tried that - and now the error has changed (and this is why I've been banging my head on the wall!!)

Now:

If E30 is Empty (or 0 as we have discovered) the code traps the error. Yay!

However...

If E30 is “tbc” I get a "Run-time Error '13' - Type mismatch" on that line of code. Not yay!

 

[Fluff]

I was forgetting that it was a string so it should be "0" rather than 0

I would also recommend changing End to Exit Sub, as End will kill any global variables if you use them.