+ Reply to Thread
Results 1 to 7 of 7

Macro that converts today()+3 in to text on 2 columns

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    10

    Macro that converts today()+3 in to text on 2 columns

    Hi all,

    I had a perfectly working macro for 1 column which when the work sheet is saved, the macro converts any formulas on a column that has today()+3 in, into a text, so when it saves, the date remains static.

    I now need this macro to do the same job within 2 columns, however, it fails to pick up on the second column. This is my macro so far:

    Please Login or Register  to view this content.
    Any help would be greatly appreciated it.

    The original macro that works perfectly on 1 column is:
    Please Login or Register  to view this content.
    Last edited by phish3rz; 12-28-2012 at 05:56 AM. Reason: Solved!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro that converts today()+3 in to text on 2 columns

    The code looks fine - is it throwing up any errors? Have you tried putting a debug in and stepping through it (perhaps with smaller ranges).

  3. #3
    Registered User
    Join Date
    12-07-2012
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Macro that converts today()+3 in to text on 2 columns

    It's throwing no errors up unfortunately. The weird thing what's happening, is that only DateRng2 is working, it appears to ignore the first for loop which I require for the L column..but then moves onto the second for loop and works fine!

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,516

    Re: Macro that converts today()+3 in to text on 2 columns

    Does the ranges ("B5:B36665") and ("L5:L36665") contains date?

  5. #5
    Registered User
    Join Date
    12-07-2012
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Macro that converts today()+3 in to text on 2 columns

    Not all of them. If a user fills out A1, then B1 is filled with a today(). If a user fills out K1, then L1 is filled in with today()+3, so that macros intention is to convert the formula into text so the date is static. It does this correctly, how ever it's ignoring the first for loop.

  6. #6
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro that converts today()+3 in to text on 2 columns

    After you've set DateRng just stick in a line:

    Please Login or Register  to view this content.
    And make sure it has the correct range in there (although I can't see any reason why it shouldn't)

    If that's OK change the line to:

    Please Login or Register  to view this content.
    Which should return 36,661.

    Out of interest, you've said that not all of the cells contain a date - what's in the cells that don't contain one? Is there any reason you couldn't just use a PasteSpecial for the whole range at once, it would be much, much faster than looping through every cell.

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,256

    Re: Macro that converts today()+3 in to text on 2 columns

    I don't see a problem with the code, and I tested it my own test workbook and it worked fine. After that test, I modified it to be much simpler (probably faster too). You may want to try this version to see if it works better for you:
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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