Why won't this run with IF statement & MsgBox where it is

[Brian Feth]

Window 11, Excel 2021 Why wont this run with the If statement (between the question marks) where it is. It runs fine without the If Statement. I get the message "Compile Error: Expected End Sub" while highlighting the "H" in the line, " If Range("H" & FirstVender2Row).Value = "Missing Vender" Then ". Help is always appreciated!

For x = 0 To NumVender1ToFill
ChosenDescript = Range("J" & FirstVender1Row).Value

Do Until FirstVender2Row > LastVender2Row
Vender = Range("H" & FirstVender2Row).Value

'????????????????????????????????????????????????????????????????????
If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
Sub MsgBoxAbortRetryIgnore()
MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
End Sub
End If
'???????????????????????????????????????????????????????????????????

If InStr(ChosenDescript, Vender) <> 0 Then 'Match, Positive result, True
Range("H" & FirstVender1Row).Value = Vender 'Fills Vender name adjacent to Description
FirstVender2Row = Range("H595").Row 'Resets the Vender row
FirstVender1Row = FirstVender1Row - 1
Exit Do

ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
FirstVender2Row = FirstVender2Row + 1
End If
Loop
Next x

 

[Michael M]

the variable "FirstVender2Row" doesn't appear to have a starting value ??

 

[rlv01]

It runs fine without the If Statement. I get the message "Compile Error: Expected End Sub"

That's not running fine. That's the compiler tell you there is a problem.

Sub and End Sub define the beginning and end of a subroutine, you can't use it as you are doing, inside another subroutine. Why not just comment them out?

    '????????????????????????????????????????????????????????????????????
    If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
        'Sub MsgBoxAbortRetryIgnore()
        MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
        'End Sub
    End If
    '???????????????????????????????????????????????????????????????????

 

[Scott Huish]

You can't just place a macro in another macro like that

Try this:

If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
    MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
    Exit Sub
End If

although using vbAbortRetryIgnore without testing for any of those conditions isn't doing anything. You should use vbOKOnly to use the code as written.

BTW, Vender should be spelled Vendor

 

[Brian Feth]

That's not running fine. That's the compiler tell you there is a problem.

Sub and End Sub define the beginning and end of a subroutine, you can't use it as you are doing, inside another subroutine. Why not just comment them out?

    '????????????????????????????????????????????????????????????????????
    If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
        'Sub MsgBoxAbortRetryIgnore()
        MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
        'End Sub
    End If
    '???????????????????????????????????????????????????????????????????

Window 11, Excel 2021 Why wont this run with the If statement (between the question marks) where it is. It runs fine without the If Statement.

That's not running fine. That's the compiler tell you there is a problem.

Sub and End Sub define the beginning and end of a subroutine, you can't use it as you are doing, inside another subroutine. Why not just comment them out?

    '????????????????????????????????????????????????????????????????????
    If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
        'Sub MsgBoxAbortRetryIgnore()
        MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
        'End Sub
    End If
    '???????????????????????????????????????????????????????????????????

I get the message "Compile Error: Expected End Sub" while highlighting the "H" in the line, " If Range("H" & FirstVender2Row).Value = "Missing Vender" Then ". Help is always appreciated!

For x = 0 To NumVender1ToFill
ChosenDescript = Range("J" & FirstVender1Row).Value

Do Until FirstVender2Row > LastVender2Row
Vender = Range("H" & FirstVender2Row).Value

'????????????????????????????????????????????????????????????????????
If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
Sub MsgBoxAbortRetryIgnore()
MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
End Sub
End If
'???????????????????????????????????????????????????????????????????

If InStr(ChosenDescript, Vender) <> 0 Then 'Match, Positive result, True
Range("H" & FirstVender1Row).Value = Vender 'Fills Vender name adjacent to Description
FirstVender2Row = Range("H595").Row 'Resets the Vender row
FirstVender1Row = FirstVender1Row - 1
Exit Do

ElseIf InStr(ChosenDescript, Vender) = 0 Then 'No match, negitive result, False
FirstVender2Row = FirstVender2Row + 1
End If
Loop
Next x

I wrote “Why won’t this run WITH the If statement. It runs fine WITHOUT the If Statement”. Followed by “I get the message "Compile Error: Expected End Sub…….”. I realize the computer is telling me there is a problem. That’s why I submitted a question.

 

[Brian Feth]

You can't just place a macro in another macro like that

Try this:

If Range("H" & FirstVender2Row).Value = "Missing Vender" Then
    MsgBox "Missing Vender, Press Abort, Hand Fill in Vender", vbAbortRetryIgnore
    Exit Sub
End If

although using vbAbortRetryIgnore without testing for any of those conditions isn't doing anything. You should use vbOKOnly to use the code as written.

BTW, Vender should be spelled Vendor

That works as intended. Thank you so much! At some point I realize I had written both "Vender" and "Vendor" in different places in the program so I looked it up. My Dictionary said "Vendor" is the English spelling and "Vender" is the American spelling so I picked one. Thank you again.