+ Reply to Thread
Results 1 to 18 of 18

Add 2 different times into 2 cells based on if a cell is blank or not.

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Add 2 different times into 2 cells based on if a cell is blank or not.

    Morning happy campers!

    Attached is a sample showing desired result.

    5 Tabs - mon-Thu + Final Result (collation of whole week) (current sheet is same layout as tab - Result Final, only 1 tab in workbook.

    My current code will place the data in columns B, E, H, K, N, Q, S, W (based on what day it is run) (courtesy of Arlu!)

    I would like a message box with yes or no option (this alone i can do)
    -BUT-
    What i am unsure on is getting the response from the yes/no option to either run a 2nd macro or not.
    This also needs to be tied into the days date.

    So, i run my code as normal on Mon, data appears in Columns B & E (as on Mon tab), then, i would like the message box to appear (text can be anything as i will change) and if yes is selected then the following will happen: (in cell C6) =If(E6 is blank, show me nothing, if not, show me "06:00") -ALSO- (in cell D6) =if(E6 is blank, show me nothing, if not, show me "14:30")
    If no is selected then macro finishes without starting the 2nd one.

    Then the same on Tue, only this time the reference cell will be H6, with 06:00 & 14:30 being entered into F6 & G6.
    Then the same on Wed, only this time the reference cell will be K6, with 06:00 & 14:30 being entered into I6 & J6.
    Then the same on Thu, only this time the reference cell will be N6, with 06:00 & 14:30 being entered into L6 & M6.
    Then the same on Fri, only this time the reference cell will be Q6, with 06:00 & 14:30 being entered into O6 & P6.
    Then the same on Sat, only this time the reference cell will be T6, with 06:00 & 14:30 being entered into R6 & S6. (not always ran over weekend)
    Then the same on Sun, only this time the reference cell will be W6, with 06:00 & 14:30 being entered into U6 & V6. (not always ran over weekend)

    Kind Regards

    Galvinpaddy
    Attached Files Attached Files
    Last edited by galvinpaddy; 02-01-2013 at 08:31 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    You can do this in a formula instead of by code. And ensure that on every Monday the formula columns are not deleted y the macro. Can change the macro slightly for that.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    I have a formula in there at present,
    Please Login or Register  to view this content.
    &
    Please Login or Register  to view this content.
    i guess i may have been trying to over complicate it with too much detail lol

  4. #4
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Arlu,

    Could you ammend the code so that on a monday the following columns are cleared.

    B, E, H, K, N, Q, S, W
    Its worth mentioning that not all of column B can be cleared, as B3 contains the week commencing date.

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Updated code - I think the S is a typo. Its column T that needs to be cleared -
    Please Login or Register  to view this content.
    Last edited by arlu1201; 02-01-2013 at 09:42 AM.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Also its good if you give a link to your previous thread so that others facing a similar problem will understand the continuity.

  7. #7
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Thanks Arlu,
    I have added your new code, but when i run i receive a compile error. "End with without with"

    And i agree, for anyone wishing to see teh build up to this request, please drop along to
    http://www.excelforum.com/excel-prog...ed-2-date.html

    Again, all thanks to Arlu for the great coding and brilliant support!

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    I have edited the code.

  9. #9
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Arlu,
    On the sheet you previously provided, if you past that code, and change the date in tab "Pete" to =TODAY()+3 and run it, there is a slight issue, the code inserts the area in Column A, for Monday, the area needs to be in Column E.
    Column A simply has a formula to give numeric value next to each cell to allow ease of idetifying how many heads in total used over the week.

    Kind regards.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Should i use the same file as the one in the linked thread?

  11. #11
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    yes please with the latest code you provided (when you sent me the sheet)

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    I ran the code from post 5 in this thread and it worked fine.

    Did you change the week commencing date?

  13. #13
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    And that my friend would be a no, because im a plonker lol.
    Sorry to have questioned your coding, i was sure i had everything right my side lol

    So, yes, it works very nicely! i will amend it now to work on both AM & PM sheets. (thats embarrassing)

    Could i ask though, that you create a code as requested in post 1?
    having reviewed the sheet, i would like the coding to do it if yes is clicked from the msg box, and not to do it if no is clicked from msg box. (will keep the sheet much tidier at end of week)
    In the instance where an employee works over the normal 8 hrs, we simply click no to the msg box and can enter hours manually

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    So the results from the msgbox should over-write the formulae already input there?

  15. #15
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    possibly yes.
    I think i would run it with no formula in cells and compare it, will test it at work and see what happens either way

  16. #16
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Possibly one last question - the msgbox should appear for each row of data in that sheet for that day?

  17. #17
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Good morning Arlu!
    Thinking about it that may be a bit excessive.

    Can you try one code with that feature, and one where it only asks as the last time is entered (if thats A) possible & B) explained properly)

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Add 2 different times into 2 cells based on if a cell is blank or not.

    Maybe you can explain what you are trying to achieve with the messagebox. What is your message going to be? It will help me understand what you are trying to do.

+ 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