+ Reply to Thread
Results 1 to 11 of 11

Code doesn't find a date if it is a formula, but does find hard coded date

  1. 02-14-2018, 04:34 AM #1
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Code doesn't find a date if it is a formula, but does find hard coded date

    Hi Gurus,

    I have a piece of code that moves values from one sheet to another. Part of a code is to set X & Y (dates and products). Y works just fine. X only works if the dates on a target sheet are hard coded, but once I change them to a TODAY()+1 style formula, code does not find X. Does anybody have a clue where the code needs amending to identify date formula?

    Please Login or Register  to view this content.
    A million thanks in advance!
    Reply With QuoteRegister To Reply
  2. 02-14-2018, 04:55 AM #2
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    try amending:
    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.
    It can find the correct value, but it may also find something you do not want
    Last edited by kev_; 02-14-2018 at 05:00 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear
    Reply With QuoteRegister To Reply
  3. 02-14-2018, 05:02 AM #3
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    I have tried this already, no luck...
    Reply With QuoteRegister To Reply
  4. 02-14-2018, 05:11 AM #4
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Can you attach your workbook
    It finds the correct value for me so there must be something different in how your values are held

    Thanks
    Reply With QuoteRegister To Reply
  5. 02-14-2018, 05:17 AM #5
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Attached. You will need to add some values in row 35 on Sheet1 and hit PLAN button. The values should be moved to Sheet2.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-14-2018, 05:20 AM #6
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Sorry, values are moved from Sheet MAIN to Sheet BATCH PLAN.

    Also, the code works if on Sheet BATCH PLAN dates are hardcoded.
    Reply With QuoteRegister To Reply
  7. 02-14-2018, 05:40 AM #7
    kev_
    kev_ is offline
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Your last 2 posts are confusing
    To save me having to puzzle it out..

    It would be easier if you just tell me exactly what to enter (into which cell and which sheet)
    I need
    - one example of something that works
    - one example of something that does not

    thanks
    Reply With QuoteRegister To Reply
  8. 02-14-2018, 05:53 AM #8
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    As I mentioned earlier, add some numeric values in row 35 (green highlighted) on sheet MAIN and hit PLAN button. Those values should now be moved onto sheet BATCH PLAN against the correct product and correct dates. I have simulated working and not working situations. In a working version dates on sheet BATCH PLAN are hardcoded, while in a not working version they are formulas instead.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-14-2018, 06:15 AM #9
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,908

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Dates are tricky in XL.
    If I use this there's no problem to find correct column.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  10. 02-14-2018, 06:20 AM #10
    ABSTRAKTUS
    ABSTRAKTUS is offline
    Forum Contributor ABSTRAKTUS's Avatar
    Join Date
    04-18-2010
    Location
    England
    MS-Off Ver
    Win10 Excel 2016
    Posts
    609

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Fabulous piece of code, thanks bakerman2!
    Reply With QuoteRegister To Reply
  11. 02-14-2018, 06:25 AM #11
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,908

    Re: Code doesn't find a date if it is a formula, but does find hard coded date

    Glad to help and thanks for rep+.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Find'Find next using variable, not hard coded
    By Go Cats in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2017, 05:53 AM
  2. VBA Find'Find next using variable, not hard coded
    By Go Cats in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-08-2017, 07:33 PM
  3. Formula / Code to find date within strings
    By ivhee00 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-23-2016, 06:12 AM
  4. [SOLVED] Adding a hard coded time (hh:mm) to the end of a DATE formula within the same cell
    By symaxf in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-16-2015, 08:22 PM
  5. Replace hard coded date with variable.
    By Steve2107 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-27-2015, 08:58 PM
  6. [SOLVED] vba code to find Duplicate Values and Newest date(Max) date in the Corresponding Column(ce
    By Pradu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2013, 05:20 AM
  7. [SOLVED] Find the date and copy the values from the colum of the date find and paste to other sheet
    By nizzcmzph in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-20-2013, 10:03 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1