+ Reply to Thread
Results 1 to 5 of 5

Enter date and Number to find the result

  1. #1
    Registered User
    Join Date
    06-20-2012
    Location
    Limerick Ireland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Enter date and Number to find the result

    Hi Guys,
    Just new to VBA, so I hope you can help me on a problem I am having!

    I created a spreadsheet that I use for Hat Hire where I indicate on the intersection between the Date and the Hat SKU Number what Days the Hat is out on Hire with the number "1". I have Dates listed on the first row and I have the Hat SKU numbers on the first Column. I have attached a sample Spreadsheet to give you a better Idea of what I am talking about.

    Firstly I am looking for help with code whereby if I enter a Date "Textbox1" eg: 02/06/2012 and a Hat SKU Number "Textbox 2" eg: SKU101 on a userform it will enter the number "1" in the intersecting cell as shown below.

    Secondly if I enter a Date "Textbox1" and a Hat SKU Number "Textbox 2" on a different userform then if it finds the number "1" it will display "Hired" in "Textbox 3" and if it find the cell blank it displays "Available" in Textbox 3.

    Hope you can help guys?






    01/06/2012 02/06/2012 03/06/2012 04/06/2012
    SKU100
    SKU101 1
    SKU102
    SKU103

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Enter date and Number to find the result

    sorry, i dont see an attached workbook?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-20-2012
    Location
    Limerick Ireland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Enter date and Number to find the result

    Thank you so much for your reply!
    Please find attached the sample Spreadsheet I forgot to attach.
    The sample spreadsheet will give you an idea of what I am talking about.

    Hope you can help

    regards

    Derek
    Attached Files Attached Files

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Enter date and Number to find the result

    The formula for that would be:

    =IF(INDEX(B2:F7,MATCH("SKU"&H1,A2:A7,0),MATCH(I1,B1:F1,0))=1,"Hired","Available")
    where H1 would hold the SKU number and I1 would hold the date

    Does that get you on the right track for your VBA conversion of it?

  5. #5
    Registered User
    Join Date
    06-20-2012
    Location
    Limerick Ireland
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Enter date and Number to find the result

    Hi Cutter,

    Thanks for reply but unfortunitly I am looking for the VBA code for this problem. I will be using userforms as described below.

    Firstly I am looking for help with code whereby if I enter a Date "Textbox1" eg: 02/06/2012 and a Hat SKU Number "Textbox 2" eg: SKU101 on a userform and when I press save it will enter the number "1" in the intersecting cell as shown below.

    Secondly if I enter a Date "Textbox1" and a Hat SKU Number "Textbox 2" on a different userform then if it finds the number "1" it will display "Hired" in "Textbox 3" and if it find the cell blank it displays "Available" in Textbox 3.

    Hope you can figure out what I am trying to do??

    regards

    Derek

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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