Simple VBA question (i think)

[deletedalien]

i have this line of code:

               If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1
Exit1:
              Application.ScreenUpdating = True
               MsgBox "Error", vbCritical
               Exit Sub

and the following formula in Summary inbound A1
=IF(COUNTIF('Inbound Detail'!AF1:AH1,"error")>0,"ERROR","Ok")

and for whatever reason regardless if A1 says "ok" or "error" it always triggers Exit1 sub routine.

What am i doing wrong?

Thanx in advance.

 

[Jonmo1]

VBA code still just executes one line at a time, and in order.

Exit1: isn't a 'Sub'. It's just the next line of code.


The purpose of the Then Goto Exit1 would be to make the code NOT execute something..
That line is going to run no matter what.

Here is a more appropriate usage of it.

If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1
MsgBox "Hello"

Exit1:
              Application.ScreenUpdating = True
               MsgBox "Error", vbCritical
               Exit Sub

The Message Box will be skipped if the condition is true and it then goes to Exit1

 

[JLGWhiz]

If Worksheets("Summary Inbound").Range("A1").Value = "Error"

=IF(COUNTIF('Inbound Detail'!AF1:AH1,"error")>0,"ERROR","Ok")

Some things are case insensitive, in this case, they are case sensitive and vba sees them as unequal strings. That means your statement will always be false.

 

[Caleeco]

Hello,

The Exit1 statement will always execute as there is nothing stopping excel executing that bit of code. Excel will just execute all the code top to bottom in a sub-routine. Adding once more exit sub will prevent this:

If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1


Exit Sub


Exit1:
    Application.ScreenUpdating = True
    MsgBox "Error", vbCritical
    Exit Sub

Hope that helps
Caleeco

 

[deletedalien]

Some things are case insensitive, in this case, they are case sensitive and vba sees them as unequal strings. That means your statement will always be false.

Tried... Failed...

 

[deletedalien]

Hello,

The Exit1 statement will always execute as there is nothing stopping excel executing that bit of code. Excel will just execute all the code top to bottom in a sub-routine. Adding once more exit sub will prevent this:

If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1


Exit Sub


Exit1:
    Application.ScreenUpdating = True
    MsgBox "Error", vbCritical
    Exit Sub

Hope that helps
Caleeco

That just stopped the code regardless of A1's Value

 

[deletedalien]

VBA code still just executes one line at a time, and in order.

Exit1: isn't a 'Sub'. It's just the next line of code.


The purpose of the Then Goto Exit1 would be to make the code NOT execute something..
That line is going to run no matter what.

Here is a more appropriate usage of it.

If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1
MsgBox "Hello"

Exit1:
              Application.ScreenUpdating = True
               MsgBox "Error", vbCritical
               Exit Sub

The Message Box will be skipped if the condition is true and it then goes to Exit1

i also tried this with no success...

It still "sees" A1 as error regardless...

 

[Jonmo1]

Rather than picking at code that is known to not do what you want..

Can you explain with words what you actually do want the code to do ?

It sounds like you only want the code after Exit1 to run if the statement is True
This will also address the case sensitivity issue.

If UCase(Worksheets("Summary Inbound").Range("A1").Value) = "ERROR" Then
    Application.ScreenUpdating = True
    MsgBox "Error", vbCritical
    Exit Sub
End If

 

[deletedalien]

Rather than picking at code that is known to not do what you want..

Can you explain with words what you actually do want the code to do ?

It sounds like you only want the code after Exit1 to run if the statement is True
This will also address the case sensitivity issue.

If UCase(Worksheets("Summary Inbound").Range("A1").Value) = "ERROR" Then
    Application.ScreenUpdating = True
    MsgBox "ERROR", vbCritical
    Exit Sub
End If

Sorry about that but, yes Exactly that

so i have a macro that copies, pastes information accordingly and what i need from his is, IF the information was pasted incorrectly and the formula in A1 will be used to validate this.

now if A1 states that there is an error the rest of the code should stop and an error message should popup basically stating that the user did something wrong.


now, i'm just testing this single "test" macro and it still always displays the error message in the vbexclamation:

Sub test()
 If Worksheets("Summary Inbound").Range("A1").Value = "Error" Then GoTo Exit1
Exit1:
              Application.ScreenUpdating = True
               MsgBox "Error", vbCritical
               Exit Sub
End Sub

Additionally, shouldn't the case sensitive issue arise when the condition is actually met?

in other words my problem constantly thinks that the word error is on A1 regardless if it reads OK or ERROR or anything else (as the result of that formula)

 

[Jonmo1]

The code after Exit1: will execute always regardless of the true/false result of your IF.
Simply because it's the next line of code after the if.
There is nothing telling VBA to 'skip' that code.