Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    I want one of my macros to print a page automatically EXCEPT when a certain cell contains one of a specific group of words. I know I can do this as follows:


    If InStr(LCase(Range(" A1")), "apple") = 0 Or If InStr(LCase(Range("A1")), "pear") = 0 Or ……etc…..Or…..etc……….Or…..etc. Then <print command>


    where cell A1 might contain the word "apple", "pear" etc. I'm not sure how many "Or"s I can string out here but I do believe they would all have to be on one line. Since there could be 20 different words that could show up in cell A1 that would make for a pretty long line. As long as one of those words were NOT in cell A1 the <print command> would activate.

    It would seem there must be a simpler way to make the macro skip past the printer command sequence if one of those words is in cell A1.

    Thanks,
    BH

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    BH,

    Try this:
    Code:
    Option Explicit
    
    Sub MyPrint()
    
    Dim zExcept(3) As String
    Dim iCntr      As Integer
    
    zExcept(0) = "APPLE"
    zExcept(1) = "PEAR"
    zExcept(2) = "ORANGE"
    
    For iCntr = 0 To UBound(zExcept) - 1
       If InStr(1, UCase([A1].Text), zExcept(iCntr)) <> 0 Then GoTo ExitMyPrint
    Next iCntr
    
      MsgBox "Your Print Code goes here", vbOKOnly, _
             "Printing..."
             
    ExitMyPrint:
    
    End Sub
    If your exception words change frequently you could store a list on a worksheet and load the array from there.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks ........much appreciated.

    No, my exception words don't change.

    Actually this isn't about fruit but rather printing invoices. I'm switching to emailing .pdf invoices to save on mailing costs of invoices to our customers. Some say ok and others say no they still want a paper copy sent by mail.

    To make this happen:

    First my macro will continue to print one paper copy of the invoice for our records.

    Then this change in the macro is going to generate the .pdf print of the invoice for those who agree to emailed invoices, and print a 2nd copy of the paper invoice to send by mail for those who don't.

    Coding for printing the .pdf to the specific customers was easy..........not printing the 2nd paper copy to them is a bit harder. Hence the cry for help here !

    Once I sort it out it should save some time and money.

    Thanks again..........I'll give your suggestion a try and report back.

    BH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •