+ Reply to Thread
Results 1 to 8 of 8

Code generating wrong values when run for multiple times (w.r.t previous solved thread)

  1. #1
    Registered User
    Join Date
    07-13-2019
    Location
    Mansehra, Pakistan
    MS-Off Ver
    2010
    Posts
    46

    Code generating wrong values when run for multiple times (w.r.t previous solved thread)

    Hello Everyone!

    Kindly refer to http://www.excelforum.com/excel-prog...r-methods.html

    When i run following code on the worksheet i attached, it produces correct results, i.e. replacing the whole dates with only "Day" for whole column untill lastrow.
    But, it reduces the "Day" by 1 in "Posting Date" column each time when i run the code again.
    For example after running the code for first time if the value was "1", running it for 2nd time produces 31, running it again would bring 30, and so on.
    Why is it reducing the value by 1 each time?

    Please Login or Register  to view this content.
    I am also attaching the sheet.

    Thanking in advance
    Attached Files Attached Files
    Last edited by Asad Mir; 06-20-2020 at 04:27 AM.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,713

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Dates in VBA start from 31-Dec-1899 unlike the sheet where it starts from 1-Jan-1900 ... Therefore, when you run your code again, day(1) = day(31-Dec-1899) so you get 31, hence 31-Dec-1899

    To test what's happening with you, you can run the below macro & watch the outcome in the immediate window

    Please Login or Register  to view this content.
    PS: Please take a moment & edit your post, select the code then press the # to insert code tags instead of quoting the code
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    439

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Change
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    ❖ Please mark your thread is SOLVED if there has been offered a solution that works fine for you.

    ❖ If you like solutions provided by anyone, feel free to add reputation by clicking on ✶ Add Reputation bottom left of their posts.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Use the code post #6 in your original thread.

    https://www.excelforum.com/excel-pro...ml#post5348311

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,411

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Simple - second time around it is no longer looking at a date as you have already filled the cell with just numbers ???????????

  6. #6
    Registered User
    Join Date
    07-13-2019
    Location
    Mansehra, Pakistan
    MS-Off Ver
    2010
    Posts
    46

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Many Thanks!
    If tbl(i, 1) > 31 Then tbl(i, 1) = Day(tbl(i, 1)) 'Format(tbl(i, 1), "d")
    It does solve the problem, The values are not getting changed if i run the macro more than once.
    But, I don't get to understand the logic behind this "If" Condition.
    Need help on that...

  7. #7
    Registered User
    Join Date
    07-13-2019
    Location
    Mansehra, Pakistan
    MS-Off Ver
    2010
    Posts
    46

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Use the code post #6 in your original thread.
    Thanks Jindon!
    Post # 6 do solve the problem.
    The link you shared on EVALUATE FUNCTION in the original Thread is of great help.
    But, as i am new to Excel VBA, so, for time being EVALUATE FUNCTION is hard to digest. But I can see its usefullness. I would refer back to it as soon as i am comfortable in using evaluate function.
    If you have anything else on this evaluate function, Please share. I would love to learn more about it.

    Thanks again

  8. #8
    Registered User
    Join Date
    07-13-2019
    Location
    Mansehra, Pakistan
    MS-Off Ver
    2010
    Posts
    46

    Re: Code generating wrong values when run for multiple times (w.r.t previous solved thread

    Many Thanks to all the contributors here who took their valuable time to solve the problem.

    Much Appreciated!

+ 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. [SOLVED] Google Sheets - Generating a Sequential list based on previous values
    By lalalalalala in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 9
    Last Post: 12-13-2019, 07:05 AM
  2. [SOLVED] VBA to find in multiple sheets and place near match. Previous thread: Inventory VBA
    By baxius in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-14-2019, 10:46 AM
  3. [SOLVED] Link of thread "Mark thread as solved" Sticky
    By ImranBhatti in forum Suggestions for Improvement
    Replies: 2
    Last Post: 02-14-2017, 08:31 AM
  4. [SOLVED] Follow up error message from previous solved thread
    By RachelMads02 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 05-16-2016, 04:08 PM
  5. [SOLVED] Uable to change the title of my thread; [SOLVED] Textbox use with numerical values
    By rodalsa1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2015, 09:37 AM
  6. Replies: 4
    Last Post: 01-18-2012, 09:55 PM
  7. [SOLVED] [SOLVED] Delete row containing all previous values.
    By mohd21uk via OfficeKB.com in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-10-2006, 09:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1