+ Reply to Thread
Results 1 to 11 of 11

Skip macro if year in cell is smaller than this year

  1. #1
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Skip macro if year in cell is smaller than this year

    Hello all,

    Since i am a newbie at vba, i need some help adapting one small portion of my code.
    So basically i have the following code that looks on sheet "req" check the date in req!a21, go back to Sheet1, delete everything in column I starting from date in req!a21 and import the numbers from req!b21 to Sheet1 depending on the dates.
    Since until this year our file was containing previous years too, everything was working just fine. Now, we start everything from scratch and we didn't include previous years.

    In the attached file everything is working fine, but if i change the date in req!A21 to 08.12.2019 (the date of the last request could be from last year, or 2018...etc), the macro returns an error since it cannot find the date from req!a21 on sheet1 column B.

    I am trying to change the code a little bit, that if the year in req!A21 is older that this year to do nothing and go to the next macro in queue.

    This is my code and please check the demo attached file.

    Please Login or Register  to view this content.
    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Skip macro if year in cell is smaller than this year

    Will all the cells in column A in the "req" sheets always have the same year? In other words, if you change the date in req!A21 to 08.12.2019, will A22:A28 also have the year 2019? Will the data in Sheet1 always start in row 6 and will the data in req always start in row 20?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    Hello Mumps1,
    No, the cells in "req" column A, don't always have the same year. Sometimes A21 can be 2019, sometimes 2020, sometimes 2017, etc. This cell essentially represents the date of the next request from client. For example if the request was updated in 01.01.2019, the date of the next request (in A21) can be 20.12.2019, the following one can be 15.02.2020 and so on. Or sometimes all the dates can remain 2019 if the client didn't update the request - meaning the client don\t need our products anymore and the request remains there...

    Maybe another solution is to make the code look in req, range A20:A1000, find the first cell in range with the same year as this year, go to Sheet1 column I, delete the column starting from the date found in req and paste the data from req starting from the first cell in req!a21:a1000 where the year is this year...
    But this is already over my head...

    Yes, the data in sheet1 will always start in row 6 and in req will always start in row 21 (A20 = table header, A21 = the date of the next request)
    On req i have some other data before A21, like the total requests in this year, the last delivery note received by client, etc, but our range of interest will always start from A21.

    Thank you for your time
    Last edited by lofgren; 01-24-2020 at 02:56 AM.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Skip macro if year in cell is smaller than this year

    Try:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    Thanx for your answer,
    I just test it and if A21 is 2019 i get this error "Run-time error 91 Object variable or With block variable not set" on this line "fndDate.Offset(, 7) = rDate.Offset(, 1)"

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Skip macro if year in cell is smaller than this year

    Oops! Replace this line of code:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    On the test file it's working just perfect
    I will test it on my main file as soon as i get home and get back to you.

    Thank you very very much

  8. #8
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    Hello again,

    I just test the macro on my main file and it seems that it doesn't import all the quantities.
    Please see the attached file.

    Any idea why is this happening?
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    I wrote some random quantities in second sheet and it seems that the macro is importing only the first 9 rows from every month...
    Please see attached file
    Attached Files Attached Files

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: Skip macro if year in cell is smaller than this year

    The destination column in your new file has changed from I to C.
    Replace this line of code:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    10-06-2017
    Location
    Europe
    MS-Off Ver
    2013
    Posts
    123

    Re: Skip macro if year in cell is smaller than this year

    It seems that at home everything is ok...
    I will try it again tomorrow at work

+ 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] sumproduct to count year values in range based on another cell year value
    By mr_mango81 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-04-2017, 12:46 AM
  2. How change cell year dates to automatically update to new year
    By RonRich in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2015, 08:29 AM
  3. vba to add year to a cell, then be able to increase or decrease year to suite need
    By thecdnmole in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2014, 06:56 PM
  4. Automatically update year interval cycles from year to year
    By trumptight in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2014, 10:38 PM
  5. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  6. [SOLVED] Macro to Retrieve Data with Start Year and End Year
    By boldcode in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2013, 01:09 PM
  7. Macro to Skip Rows between the current value and a selected value, and add a year
    By jll5ff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-19-2013, 06:11 PM

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