+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup

  1. #1
    Registered User
    Join Date
    11-07-2011
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2016
    Posts
    18

    using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup

    Im having a little trouble with a part of my workbooks and im hoping someone has the experience to point me in the right direction. im using a formula:

    =IF(ISNUMBER(MATCH(A6,'[Members.xlsm]Member Entry'!A10:A700,0)),"1","")

    right now if the statement is true then it simply enters a 1 into the cell, What i would like it to do is if it returns true, i would like it to look in another cell in the same row where it found the match, find the value in that cell, and subtract 1 from that value, then display the updated value. Can anyone tell me if this is possible? im open for suggestions also, if you can think of a better cleaner way to do this, im open.

    Im also having an issue with: CreateObject("WScript.Shell").Popup "Thank You and Enjoy Your Class.", 2, "Thank You!" it doesnt dissappear after the 2 sec like it should, any ideas??

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup

    =IF(ISNUMBER(MATCH(A6,'[Members.xlsm]Member Entry'!A10:A700,0)),INDEX('[Members.xlsm]Member Entry'!B10:B700,MATCH(A6,'[Members.xlsm]Member Entry'!A10:A700,0))-1,"")

    where B10:B700 is the column you want to extract from before subtracting 1.

    The second question should be asked separately in the Programming forum.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    11-07-2011
    Location
    Wisconsin USA
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup

    OK, i posted the other problem in programming. Now back to this issue, That worked but i dont think i explained it well enough. What i need to happen is the result needs to be entered into the cells, Using your example, B10:B700 (the actual cells are '[Members.xlsm]Member Entry'!E10:E700) Im building a member check in system based on credits so basiclly what happens here is the members check in, the system checks to see if they have available credits, if credits are available, their information is entered onto a sheet for reporting, a credit needs to be deducted, the formula above, and they are welcomed into the class with a popup message thanking them. (quick laymans explaination)

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup

    To subtract a value from a cell already containing a value, you will need VBA. And also for the pop-up you will need VBA..

    I, unfortunately, am not the VBA expert you are looking for at the moment I'm just a formula guy

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: using an ISNUMBER formula to find the result and CreateObject("WScript.Shell").Popup


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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