Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search function in a macro (Excel XP)

    Hi again!

    I've got my label printing situation pretty much worked out now. At this point, among other things, when I run a macro it prints a given number of labels based upon a quantity number that is in a specific cell. This is all working fine.

    I want to take this one step further and have the labels NOT get printed when the word "layer" appears in cell C10.

    In the spreadsheet I can use the following to achieve a desired result when the word "layer" or "strips" is in that cell:

    =IF(OR(ISNUMBER(SEARCH("layer",C10)),(ISNUMBER(SEA RCH("strips",C10)))),J12+1,"")

    The question is what coding to use within the macro to get the label printed or not printed depending upon whether the word "layer" is in C10.

    Hope I've explained this clearly enough.

    Thanks,
    BH Davis

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search function in a macro (Excel XP)

    <code>
    If Range("C10").Value = "layer" Then Exit Sub
    </code>
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search function in a macro (Excel XP)

    I think he wanted them to NOT print if the word "layer" is in C10.
    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search function in a macro (Excel XP)

    You can use something like this:

    If InStr(LCase(Range("C10")), "layer") = 0 Then
    ' code to print
    End If

    The InStr function returns a positive number indicating the position of the word "layer" in the value of C10, or 0 if the word "layer" is not found.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search function in a macro (Excel XP)

    You're confusing me! Doesn't my code do that?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search function in a macro (Excel XP)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    I read it too quick.
    Legare Coleman

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

    Re: Search function in a macro (Excel XP)

    Now take it easy on each other guys!!!! heh heh.........

    Legare,

    If Range("C10").Value = "layer" Then Exit Sub

    Works just fine. Thanks for getting me going with that.

    Hans, since that worked I didn't give your solution a try yet as I think it won't deal with the problem as explained below.

    I should have been more clear on one point. The word "layer" does not appear in C10 by itself. It can be "layer a", layer b", "layer 1" etc. That is why I used the example of


    ISNUMBER(SEARCH.............

    above. I need to be able to see if the word "layer" is a part of the value of the cell. It never is just the word "layer" by itself.

    Sorry.......

    BH

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search function in a macro (Excel XP)

    Legare's suggestion tests whether the cell value is equal to "layer". Mine tests whether the cell value contains "layer".

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search function in a macro (Excel XP)

    From this description, it sounds like Hans' solution does what you want and mine does not. My code checks to see if the cell contains "layer" by itself, Hans' code checks to see if the cells contains "layer" anywhere in the cell.
    Legare Coleman

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

    Re: Search function in a macro (Excel XP)

    Figures.........50/50 chance and I grab the wrong one!

    Hans, does "Lcase" mean "lower case"? If it could be upper case or lower case should I just leave that out if it does?

    Thanks,
    BH

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search function in a macro (Excel XP)

    LCase converts a string (here the cell value) to lower case. It ensures that the comparison is case insensitive. If you are certain that your cell will always contain "layer", and never "Layer" or "LAYER" or "laYeR", or if you only want to skip printing if the cell contains "layer" in lower case, you can omit it.

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

    Re: Search function in a macro (Excel XP)

    Hans,

    Nope, it will almost always be something like "Layer" so I'll leave it in.

    Thanks again............I'll let you know how it goes.

    BH

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

    Re: Search function in a macro (Excel XP)

    Okay, that worked great! Now, last step. I thought I could get this one to work myself but am not getting anywhere.

    There are two forms per sheet of printed paper on these Layout Sheets. I need one label to print for each of the two forms. If the word "layer" appearss in the above mentioned cells on either form then the macro does not print a label for that form. That is good........thanks.

    However, sometimes the sheet only contains information on one product, and the second form remains blank. What I'm getting for a result from the code below is an error message saying the printer can only accept a print quantity between 1 and xxxxxxxxxxxx (some very high number) for that second form. That is logical and I understand why.

    So, I've been trying to write in a second IF statment that will skip the label for the second form altogether if the QUANTITY cell on that form is blank. That is, nest the two IF statements. As I said, I'm getting nowhere though.

    Here is the code from the macro for printing the second label:



    ' The following line blocks printing the label if "Layer" is in C32.

    If InStr(LCase(Range("C32")), "layer") = 0 Then

    ' The following prints Label 2

    Range("AR94:AW98").Select
    Application.ActivePrinter = "DYMO LabelWriter 400 on Ne06:"
    Selection.PrintOut Copies:=Range("C34"), ActivePrinter:= _
    "DYMO LabelWriter 400 on Ne06:", Collate:=True
    Application.ActivePrinter = "Brother MFC-8440 USB on Ne08:"





    Application.ActivePrinter = "Brother MFC-8440 USB on Ne08:"
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$R$60"
    With ActiveSheet.PageSetup

    End With
    End If

    Range("C32").Select


    Note: I know I've got the Brother printer listed twice at the end. For some reason the print layout lines from the label printer stay on screen if I don't do that. It's a very annoying pattern to look at.

    Anyway, can I add a nested IF so the second label is ignored if cell C34 (the second form quantity cell) has no entry? Or is there a better way to do this?

    Thanks,
    BH

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search function in a macro (Excel XP)

    You can add a nested If ... End If to test if C34 is not blank, or combine the conditions in one If statement:

    If InStr(LCase(Range("C32")), "layer") = 0 And Range("C34") > 0 Then

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

    Re: Search function in a macro (Excel XP)

    Edited by HansV to make URL clickable

    Hans & Legare,

    I really want to sincerely thank the two of you for all your help on this (and previous) issues.

    The sheet we've been working on is the primary layout sheet for my business. Every single curved moulding we make has one of these layout sheets created prior to construction. That makes for thousands in the course of a year. By going to this label system the guys in the shop will cut down the time it takes to identify each moulding by over 50%. Up until now we've been using an ink stamp that places a fill in the blanks stamp on the back of each moulding. There are about 10 bits of information to fill in so labeling each moulding takes a minute or two depending upon how complex the moulding was. By going to these stick on labels over half the information they've been writing out is now automatically put on the label by Excel. Over time this will add up to a huge time .........and subesquently money........savings.

    I really know nothing about the two of you other then that you are both extraordinarilly generous with your time and knowledge. If curious you can see our final products at:

    http://www.curvedmouldings.com

    The most interesting stuff is at the PROJECT PHOTOS link towards the center of the home page.

    Well, thank you again and be well until next time !!!

    BH Davis

Posting Permissions

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