CODEWITHSUNDEEP
excelvbais-empty
SaraDeeCee
SaraDeeCee

Reputation: 13

Print Worksheet When Cell Is Not Blank

Okay, I am at my wits end with this one. I've searched all over for the right code to make this work but nothing has worked so far. I'm beginning to suspect that something is wrong with my syntax in another portion of the code that is overriding my IF statement. All suggestions are welcome.

What I Want: I have a program that prints multiple purchase orders for different companies from one order form. Sometimes we do not order anything from one company, so I don't want the code to allow that order form to be printed. I thought that by checking if a certain cell is blank that it would stop the printing. In the code below "D14" is usually the QTY to be ordered, so it is a numerical value. But nothing is working so far. The code is still allowing all the possible order forms to be printed even when there is nothing to order and cell "D14" is empty.

Things I've tried so far:

    If Not IsEmpty(PO.Range("D14")) Then
    PO.PrintOut
    End If

and

    If PO.Range("D14") = IsEmpty(False) Then
    PO.PrintOut
    End If
    End With

and 

    If PO.Range("D14") <> "" Then
    PO.PrintOut
    End If
    End With

Here's the whole block of code so you can get a better picture of what I'm trying to accomplish.

    Private Sub cmbPrintEchoFrance_Click()
    Dim OrderForm As Worksheet
    Dim PO As Worksheet
    Dim SoapList As ListObject
    Dim lRow As Long
    Dim rngToCopy As Range, rRange As Range

    Set OrderForm = Worksheets("ORDER FORM")
    Set PO = Worksheets("PRINT ORDER")

    Set SoapList = Worksheets("ORDER FORM").ListObjects("SOAP_LIST")
    Application.ScreenUpdating = False
    With OrderForm
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rRange = .Range("A1:A" & lRow)

    'Remove any filters
    .AutoFilterMode = False

        With rRange
        'print the Australian Natural Soaps order
            .AutoFilter Field:=3, Criteria1:="AUSTRALIAN SOAPS"
            .AutoFilter Field:=1, Criteria1:="<>"
            .Range("A1:A" & lRow).Copy PO.Range("D14")
            .Range("B1:B" & lRow).Copy PO.Range("E14")
            .Range("D1:D" & lRow).Copy PO.Range("F14")
            PO.Range("E12").Value = "AUSTRALIAN NATURAL SOAPS"
            If PO.Range("D14") = IsEmpty(False) Then
            PO.PrintOut
            End If
        End With

          'clear print order
        PO.Range("D14:F84").Clear
        PO.Range("D14:F84").ClearFormats

        With rRange
        'print the Echo France order
            .AutoFilter Field:=3, Criteria1:="ECHO FRANCE"
            .AutoFilter Field:=1, Criteria1:="<>"
            .Range("A1:A" & lRow).Copy PO.Range("D14")
            .Range("B1:B" & lRow).Copy PO.Range("E14")
            .Range("D1:D" & lRow).Copy PO.Range("F14")
            PO.Range("E12").Value = "ECHO FRANCE"
            If Not IsEmpty(PO.Range("D14")) Then
            PO.PrintOut
            End If
        End With


'clear print order
PO.Range("D14:F84").Clear
PO.Range("D14:F84").ClearFormats

With rRange
    'print the european soaps order
    .AutoFilter Field:=3, Criteria1:="EUROPEAN SOAPS"
    .AutoFilter Field:=1, Criteria1:="<>"
    .Range("A1:A" & lRow).Copy PO.Range("D14")
    .Range("B1:B" & lRow).Copy PO.Range("E14")
    .Range("D1:D" & lRow).Copy PO.Range("F14")
    PO.Range("E12").Value = "EUROPEAN SOAPS"
   If Not IsEmpty(PO.Range("D14")) Then
    PO.PrintOut
    End If
End With

 'clear print order
PO.Range("D14:F84").Clear
PO.Range("D14:F84").ClearFormats

With rRange
    'print the la lavande order
    .AutoFilter Field:=3, Criteria1:="LA LAVANDE"
    .AutoFilter Field:=1, Criteria1:="<>"
    .Range("A1:A" & lRow).Copy PO.Range("D14")
    .Range("B1:B" & lRow).Copy PO.Range("E14")
    .Range("D1:D" & lRow).Copy PO.Range("F14")
    PO.Range("E12").Value = "LA LAVANDE"
    If Not IsEmpty(PO.Range("D14")) Then
    PO.PrintOut
    End If
End With

 'clear print order
PO.Range("D14:F84").Clear
PO.Range("D14:F84").ClearFormats

    End With
    SoapList.Range.AutoFilter Field:=1
    SoapList.Range.AutoFilter Field:=3
    Application.ScreenUpdating = True

End Sub

Any suggestions you have would be appreciated!

Upvotes: 0

Views: 312

Answers (3)

PaichengWu
PaichengWu

Reputation: 2689

please try

if po.range("d14").value <> "" then
    the action
end if

Edit:

I have tried the code below on a blank worksheet.

Sub test()
    If Range("A1").Value = "" Then
        MsgBox "A1 is blank."
    End If
End Sub

The output will be enter image description here

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166685

Try: 

Debug.Print "D14 is = '" & Trim(PO.Range("D14").Value) & "' (Len=" & _
            Len(Trim(PO.Range("D14").Value)) & ")"
If Len(Trim((PO.Range("D14").Value)))>0 Then
    PO.PrintOut
End If

Upvotes: 1

kpg987
kpg987

Reputation: 486

Tim's answer is the most robust. You can also try: 

If PO.Range("D14").Value<> vbNullString Then
    PO.PrintOut
End If

Upvotes: 0

Related Questions