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

[Cyberpawz]

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 f Is Nothing 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

[StevenM]

Instead of:

   Set f = .Cell("Q23")
        If f Is Nothing Then

I think what you want is:

If IsEmpty(.Cell("Q23")) then

Asking if "f Is Nothing" (or "Not f Is Nothing") only determines whether or not the range ('f') has been "set."

[Cyberpawz]

No go, Says Method or Data not found... and it highlights the row I just changed.

[StevenM]

How about:

    With wsInt
        If IsEmpty(.Range("Q23")) Then
            MsgBox "The data can't entered, you have not entered any data into the Sales Order field."
            Exit Sub
        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

P.S. Before "Cell" should have been "Cells" (my mistake), but even so, Cells cannot take a "Q23" type reference whereas Range can.

[Cyberpawz]

Nope, still allow entering of data with nothing in it. Here is the script as of now.

Option Explicit

Sub Sub_First_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
        If IsEmpty(.Range("Q23")) Then
            MsgBox "The data can't entered, you have not entered any data into the Sales Order field."
            Exit Sub
        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
    
    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

[StevenM]

Are you sure that wsInt.Range("Q23") is empty? Could there be an empty space character left over inside the cell which might not show? Or is there a formula inside that cell?

You might try something like:

If Trim(.Range("Q23").Value) = "" Then

Trim should takeout any spaces (it takes out leading and trailing spaces from a string).

[Cyberpawz]

Are you sure that wsInt.Range("Q23") is empty? Could there be an empty space character left over inside the cell which might not show? Or is there a formula inside that cell?

You might try something like:

If Trim(.Range("Q23").Value) = "" Then

Trim should takeout any spaces (it takes out leading and trailing spaces from a string).

I am positive, here is the page in question. Without the code. Just to show you what I am attempting to do.

[StevenM]

I ran the following test a number of times on your worksheet.

Sub TestIsEmpty()
    If Range("Q23").Value = "" Then
        MsgBox "Double Quotes Is Empty and IsEmpty is: " & IsEmpty(Range("Q23"))
    Else
        MsgBox "Double Quotes Is Not Empty and IsEmpty is: " & IsEmpty(Range("Q23"))
    End If
End Sub

And when I put a value in Q23 ("Sales Order"), and ran the above macro, it tested as not empty.
Then when I deleted a value in Q23, it tested as empty.

I then copied and pasted your code, and it ran as expected. When there was nothing in Q23, the message popped up, and when there was something in Q23 it bypassed the message.

I'm at a loss to understand your problem.

[Cyberpawz]

I ran the following test a number of times on your worksheet.

Sub TestIsEmpty()
    If Range("Q23").Value = "" Then
        MsgBox "Double Quotes Is Empty and IsEmpty is: " & IsEmpty(Range("Q23"))
    Else
        MsgBox "Double Quotes Is Not Empty and IsEmpty is: " & IsEmpty(Range("Q23"))
    End If
End Sub

And when I put a value in Q23 ("Sales Order"), and ran the above macro, it tested as not empty.
Then when I deleted a value in Q23, it tested as empty.

I then copied and pasted your code, and it ran as expected. When there was nothing in Q23, the message popped up, and when there was something in Q23 it bypassed the message.

I'm at a loss to understand your problem.

Because if you look at the code you originally suggested and what you have down here it is different format wise. There is no Exit Sub, or other parts in it like your original post. I'll try out the code you just posted and get back to you on it.