+ Reply to Thread
Results 1 to 7 of 7

Making one of two sounds depending on the result of a "if" function

  1. #1
    Registered User
    Join Date
    09-13-2013
    Location
    OTTAWA, CANADA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Making one of two sounds depending on the result of a "if" function

    Hello
    I have to scan about 30,000 bar codes in Excel which has a list of 6,000 files to remove from the shelves.
    Made a search function to compare the the list of 6,000 I am looking for.

    the list of files to archive is in column G
    I scan the bar code in column H
    column I has this formula =IF(COUNTIF(FindMe2,H3)>0,"ARCHIVE THIS!","FILE OK")
    This works fine.

    What I would like is different sounds one to confirm the scan gun did scan (sometimes bar codes are hard to read) so, one sound for scan/"FILE OK"
    another sound when "ARCHIVE THIS!" is the result.

    I found on a site a module:
    Function beepNow()
    Beep
    End Function

    And so in column "J" i have =IF(I2="ARCHIVE THIS!",beepNow(),"")
    Which give's me one beep if the file is to be Archived.

    As stated earlier, I need one sound to say bar code scanned and a different sound to say "archive this".
    Could be one beep and two beeps.

    Playing a .wav file would be nice from the C/WINDOWS/MEDIA location

    Note I have no VBA experience other knowing what it is and how to insert/ (copy-paste) code in excel.

    Thank you
    Pierre

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Making one of two sounds depending on the result of a "if" function

    Hi,

    You can try this :

    Please Login or Register  to view this content.
    The formula of cells in column J would be like this :

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Making one of two sounds depending on the result of a "if" function

    Try this

    In a module

    Please Login or Register  to view this content.
    and

    =IF(I2="ARCHIVE THIS!",beepNow(1),beepNow(2))
    Martin

  4. #4
    Registered User
    Join Date
    09-13-2013
    Location
    OTTAWA, CANADA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Making one of two sounds depending on the result of a "if" function

    Not sure what happened, am retyping my reply.
    Thank you guys, both solutions work, however a small tweek would be needed.
    since the formula in column I has this formula =IF(COUNTIF(FindMe2,H3)>0,"ARCHIVE THIS!","FILE OK") that means the default falue already in the cells is "File ok"
    when I add your proposed "if" formula to the 60,000 cells I am getting 60,000 sounds

    Can you propose an adjustment?
    Pierre

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Making one of two sounds depending on the result of a "if" function

    Try using an event instead of a formula

    Please Login or Register  to view this content.
    Paste the above into the appropriate sheet module (e.g. Sheet1)

  6. #6
    Registered User
    Join Date
    09-13-2013
    Location
    OTTAWA, CANADA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Making one of two sounds depending on the result of a "if" function

    Thank you both,
    For the last part, using an event, was not sure how to do... hopefully others will find the info useful.

    Am using Karedog's solution with the two "IF" functions below.

    I changed the formulas in column I & J to nested "IF" functions
    column I, now has =IF(H2="","",(IF(COUNTIF(FindMe3,H2)>0,"ARCHIVE THIS!","FILE OK")))
    and column J, now has =IF(I2="","",(IF(I2="ARCHIVE THIS!",playsound(1),playsound(2))))

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Making one of two sounds depending on the result of a "if" function

    You are welcome, glad I can help.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 09-05-2012, 10:23 AM
  2. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  3. Making the Tools>Options...Calculation "Manua"l Vs "Automatic" a Button.
    By Ecoich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2007, 07:50 PM
  4. [SOLVED] how do i enable a drop down list depending on result of "if" funct
    By Varun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-08-2006, 11:40 AM
  5. [SOLVED] How do I set a result of an "if" function to NULL; not 0 or ""?
    By mbrockhaus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2005, 02:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1