CODEWITHSUNDEEP
excelvba
Matt Ridge
Matt Ridge

Reputation: 3651

Setting up if cell is blank don't continue... and show a message

This code works perfectly. I only have one question, I want to make it so that if there is nothing in cell Q23 that it will not put anything into NCMR Data, and say something... the code is below of what I have, and below it is what I think I need to do to a specific section to work, can someone review and make sure I am on the right path?

Option Explicit

Sub NCMR()
    Dim i As Integer

    With Application
        .ScreenUpdating = False
    End With

    'Internal NCMR
    Dim wsInt As Worksheet
    Dim wsNDA As Worksheet

    'Copy Ranges
    Dim c As Variant

    'Paste Ranges
    Dim P As Range

    'Setting Sheet
    Set wsInt = Sheets("NCMR Input")
    Set wsNDA = Sheets("NCMR Data")
    Set P = wsInt.Range("B61:V61")

    With wsInt
        c = Array(.Range("B11"), .Range("B14"), .Range("B17"), .Range("B20"), .Range("Q23"), .Range("B23") _
                , .Range("Q11"), .Range("Q14"), .Range("Q17"), .Range("Q20"), .Range("R26"), .Range("V23") _
                , .Range("V25"), .Range("V27"), .Range("B32"), .Range("B40"), .Range("B46"), .Range("B52") _
                , .Range("D58"), .Range("L58"), .Range("V58"))
    End With

    For i = LBound(c) To UBound(c)
        P(i + 1).Value = c(i).Value
    Next

    With wsNDA
        Dim LastRow As Long

        LastRow = .Range("A" & Rows.Count).End(xlUp).Row + 1

        wsInt.Rows("61").Copy

        With .Rows(LastRow)
            .PasteSpecial Paste:=xlPasteFormats
            .PasteSpecial Paste:=xlPasteValues
            .Interior.Pattern = xlNone
        End With

        With .Range("A" & LastRow)
            If LastRow = 3 Then
                .Value = 1
            Else
                .Value = Val(wsNDA.Range("A" & LastRow - 1).Value) + 1
            End If

            .NumberFormat = "0#######"
        End With
    End With

    With Application
        .Range("A61:V61").ClearContents
        .ScreenUpdating = True
    End With
End Sub

What I want to do I think:

With wsInt
    Dim f As Range

    Set f = .Cell("Q23")

        If IsEmpty(f) Then
            MsgBox "The data can't entered, you have not entered any data into the Sales Order field."
        Else
                    c = Array(.Range("B11"), .Range("B14"), .Range("B17"), .Range("B20"), .Range("Q23"), .Range("B23") _
                , .Range("Q11"), .Range("Q14"), .Range("Q17"), .Range("Q20"), .Range("R26"), .Range("V23") _
                , .Range("V25"), .Range("V27"), .Range("B32"), .Range("B40"), .Range("B46"), .Range("B52") _
                , .Range("D58"), .Range("L58"), .Range("V58"))
        End If
End With

Upvotes: 0

Views: 4826

Answers (1)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

Maybe as simple as:

With wsInt 
    If Len(.Range("Q23")) = 0 Then
        MsgBox "The data can't be entered, you have not entered any data into the Sales Order field."
        Exit Sub
    End If
End With      'added this line for clarity

Upvotes: 1

Related Questions