+ Reply to Thread
Results 1 to 9 of 9

VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

  1. #1
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    To All readers,

    In my Excel workbook I want to collect all dates out of a database for reservations. I managed to do so, but i have problems with the formulas when the actual time has expired.
    In my workbook on Sheet1 a VBA module gives me the data where my house has "no reservation" in F1. With the formula in e.g. C4 it counts if the date in B4 exists in the list of the data in F1. When true it gives a "1" in C4. With the formula in D4 it counts the days for ''no reservation''.

    This works fine.
    The problem is that after today the past data in F1 has gone from the list (the reservation system doesn't count the days of reservation in the past) and the formula in C4 does not recognize the right data anymore. It changes the outcome of the formula from "1" to "0". So all the data before today will turn into "0" and D4 will not be filled.

    What I would like to have is a Macro - or whatelse - which copy the data extracted by my formula in e.g. C4 or D4 to Sheet2 (Formula to Value) so the data can be saved before it turn into "0" by expired date.

    I've searched plenty of possibilities in Google and tried several VBA tricks but I can't find a solution that works for me.
    P.S. I don't have much experience with programming in VBA, but in the meantime I can understand some language in it.

    Does anyone have a working solution for me?
    Thanks in advance for responding!

    I'll send an attachment of the Excel file.
    reservation holiday home.xlsm
    VBA Starter

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    If I'm missing something here about what it is you want, then let me know. But it sounds like this. Go to sheet1 highlight C4 to C13, right click choose copy, go to sheet2 highlight the same cells right click again and choose paste special values. You could do this while recording a macro.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    Skywriter, thanks for your response. I know how to copy paste special values, but the problem is that this has to be done every day. Because every day the last notification in F1 has changed due to the data that disappears when no reservations can be made any more (past history). The nearest data in F1 is that of some days later. So there has to be some programming to execute this function before the data disappears. I don't have the time to manually copy and paste this every day.
    Anyone else?

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    Quote Originally Posted by VBAStarter View Post
    Skywriter, thanks for your response. I know how to copy paste special values, but the problem is that this has to be done every day. Because every day the last notification in F1 has changed due to the data that disappears when no reservations can be made any more (past history). The nearest data in F1 is that of some days later. So there has to be some programming to execute this function before the data disappears. I don't have the time to manually copy and paste this every day.
    Anyone else?
    Did you read the part about recording a macro while doing it, you could do it for all them one time, then you could run a macro everyday, either by a button or some other means?

  5. #5
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    Skywriter, maybe I'm not clear enough in what I want. I'm sorry if this is the case. Of course i can record a macro for copying the range of cells involved and paste them on Sheet2 in a specific range.
    Maybe I can explain it in steps: e.g. checking is done on 23th of February:
    Step 1. A command in VBA is scraping data from Internet Explorer website and places it in Sheet1 F1 (these are the times that the home is still free);
    Step 2. A macro is converting the text to columns in row A resulting in all the data per column. On 23th of february it could be: 23-02-2015, 27-02-2015, 3-03-2015, 9-03-2015 and so on...
    Step 3. These data will be checked in Sheet1 on the date it concerns with e.g. =COUNTIF($F$1:$KK$1;G4). If the date is corresponding with the date in G4, the value "1" is placed in H4

    Now I run this program on the 25th of February, the results will then be:
    Step 1. As above
    Step 2. Due to the actual date (25-02-2015), the data in step will now be 27-02-2015, 3-03-2015, 9-03-2015 and so on. So the first date is gone!
    Step 3. By checking the data the corresponding formula for ''23-02-2015'' will not find the value and the result in H4 will be "0" now.

    Maybe this will clarify my problem. Or do I have to handle it in a different way by locking the cells before Today() has arrived?
    Hope it will help to solve my problem, thanks for your patience!

    VBAStarter

  6. #6
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    I understand what your formulas are doing, I've understood that since the first post I can see that and by the way one of things that's confusing matters is you keep referring to F1 and in F1 what I see is this. /*<![CDATA[*/ var arrivalDays = new Array( I don't know what that is, but yes I see your dates in Row 1. They just start in G1 on the sheet I'm looking at and extend to the right J1, M1 etc.

    I get what you are saying that when you change the dates the formulas will change and you want to preserve the value that is the result of a formula today so that you will have it for later.
    But this raises questions. Is there only certain numbers you want to preserve, for an exact example you have in cell AC4 the number 4 and I get it that the number is the result of your formula finding that exact date in AW1 and that the number is an indication of the availability of the house, so do you want that number copied into sheet2 since it could change between now and June 1, I'm assuming, if a reservation is cancelled etc.?, or do you only want the number for today's date. This meaning you only want to preserve the value in cell I11 which is to the right of today's date on the sheet that I'm looking at.

    So in brief what numbers do you want copied into sheet2 and is sheet2 the only sheet these numbers will be copied to and you just keep copying these numbers into sheet2 each day or do you want a new sheet each time you run the macro, maybe naming the sheet for the date the macro was run with it preserving the numbers for that day. This is the issue that you need to clear up and I'm sure I can write something up.
    Last edited by skywriter; 02-23-2015 at 07:04 PM.

  7. #7
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    What's left in F1 is a result of the macro (go to 'date' - 'text to Columns' - 'Convert text to Columns wizard')in the initial command it contains all what's in row 1.
    And Yes, there can be a cancelling in future times. That's the reason why I only wanted to fix the numbers according the actual date (today()).
    The results in e.g. column AC4:AC12 and all the other last column numbers (D, I, N, S, X) are the most valuable to me. They will give me the final results. By the way - I think you've noticed it already - the date in AW1 is only this time there (it's only a distribution of all the dates to row 1, and never on the same place in column on row 1).

    In the days before the actual date there will be no more changes (cancellations or something like that).
    It would be nice if all the final results from the last columns in a month (D, I, N, S, X, AC) would we projected on Sheet2. The results in the past should not change anymore. The other option gives me to much excel sheets/workbooks every time it changes.

    Am I clear enough to you to give me a working plan? For a good working plan it should contain a formula which opens the workbook everyday, copies the values and close the workbook after saving.

    Thanks in advance!

  8. #8
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    Does anybody have a solution for my problem?

  9. #9
    Registered User
    Join Date
    01-08-2012
    Location
    Zwijndrecht, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    20

    Re: VBA Copy Formula to Value on another Sheet after time (Today(o)) has expired

    No one on the forum reading this anymore? Skywriter?

+ 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. Today() Formula with Time
    By Dhook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-29-2014, 01:59 PM
  2. LOOK UP, COPY PASTE into Another Sheet Within SAME Workbook - with TODAY Date
    By CHRISOK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2014, 06:22 PM
  3. Cell Colour changes based on expired time
    By hines57 in forum Excel General
    Replies: 0
    Last Post: 07-24-2012, 09:30 AM
  4. excel today countif over expired date
    By JonnieP in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-30-2005, 06:40 PM
  5. Warning! Time has expired....
    By yh73090 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-09-2005, 06:18 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