Message box in macro giving an error

aikenhead

New Member
Joined
Apr 27, 2018
Messages
12
Hi everyone, I have a macro that working great that is activated by pressing a button. I want to add a message box at 2 different locations but when i do it, im getting errors. here is the code:
Code:
Sub ScanLHQR()


'
    If Sheets("sheet1").Range("F5").Value = "Bad" Then
        Msg Box "This Code has already been scanned"
    Exit Sub
    If InStr(1, (Range("c8").Value), "P3041096") > 0 Then
        MsgBox "This is the incorrect Label"
        Exit Sub
    Range("C8").Copy
    Sheets("LHLabelNumbers").Select
        On Error Resume Next
    Dim xCell As Range
    For Each xCell In ActiveSheet.Columns(1).Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Scan").Select
    Range("C8").ClearContents
End Sub

You can see my 2 message boxes at the top of the code. I want for them to pop up if either of these 2 conditions are met to tell the operator why there is an error. If the msg box lines are not there, the code works fine.

Any suggestions? thanks :)
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,419
Office Version
  1. 365
Platform
  1. Windows
Remove the space in your 1st MsgBox
 

aikenhead

New Member
Joined
Apr 27, 2018
Messages
12
that was my bad when i copied the code over. It didnt have the space in my excel file.

the error i am getting is : Block If without End If

When i put an End If in, then i get a different error
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
You're missing 2 End Ifs

Code:
    If Sheets("sheet1").Range("F5").Value = "Bad" Then
        Msg Box "This Code has already been scanned"
    Exit Sub
    End If
    If InStr(1, (Range("c8").Value), "P3041096") > 0 Then
        MsgBox "This is the incorrect Label"
        Exit Sub
    End If
    Range("C8").Copy
    Sheets("LHLabelNumbers").Select
        On Error Resume Next
    Dim xCell As Range
    For Each xCell In ActiveSheet.Columns(1).Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Scan").Select
    Range("C8").ClearContents
 

aikenhead

New Member
Joined
Apr 27, 2018
Messages
12
Thank you very much!

I had put the End If everywhere but there. thought it was weird it worked with no msgbox when there was no end if code.

Thank you both for the help. The file it working perfectly now
 

jproffer

Well-known Member
Joined
Dec 15, 2004
Messages
2,643
This:
Code:
If InStr(1, (Range("c8").Value), "P3041096") > 0 Then Exit Sub

will work without the "End If". It's all on one line. If you go beyond one line, it needs the End If.

So the above will work as is, but this:

Code:
If InStr(1, (Range("c8").Value), "P3041096") > 0 Then
Exit Sub
End If

Does the same thing...but needs the End If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,356
Messages
5,624,219
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top