+ Reply to Thread
Results 1 to 31 of 31

vba overwrite data if 2 condition is met ( date and if helper column does not have error)

  1. #1
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    vba overwrite data if 2 condition is met ( date and if helper column does not have error)

    there are 3 table the source the helper and the data to be pasted
    Conditions are: If date is today and there is no NA paste the values otherwise retain contents.
    please see attached.


    vbaeforum.xlsx
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Is the column with the letter variables (a,b,d,w,f) to be copied also or are they just to illustrate your problem?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    no it stays it's like updating the values of a,b,d,w,f


    a,b,d,w,f the First table column A,b, c will be updated manually.
    the second table colored yellow is the search(helper table)
    the 3rd is where the data will be pasted. -> this is the update so when date is today ->update the 3rd table if it is found in the second(helper table)

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    the helper table has only 5 lines while the other two have 6
    is that by design or is helper just missing one line?

    so in this example only sample 5 is to be copied over the sample 5 in the 3rd table?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    just missing one

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Please Login or Register  to view this content.
    ok try this

    Please Login or Register  to view this content.
    with paste values instead of copy formula
    Last edited by humdingaling; 07-12-2013 at 01:42 AM.

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Maybe:
    Please Login or Register  to view this content.

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    thanks to both of you.
    they both meet the requirement

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Or:

    Please Login or Register  to view this content.
    This method will prevent from copying the date if its not supposed to move.

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    as an option (with helper column in green)
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    yup the loop is shorter nilem. ..
    thanks all.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,824

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    This should do what you want
    Please Login or Register  to view this content.

  13. #13
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    OK. will test it. on larger scale. thanks jindon.

  14. #14
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    @ jindon it's copying the data even date is not yet reached say date is july 20, 2013 that's the time it will copy paste the data.

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

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    OOps, missed that condition
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  16. #16
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Ok thanks.

  17. #17
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Sorry guys what Am i missing in the code.
    it's not copying though with the sample here.
    Last edited by vlady; 07-12-2013 at 11:09 PM.

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

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    What are you trying to do?

  19. #19
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Same thing as above but with separate sheets. copy data if date is today but i get errors when i add here

    Please Login or Register  to view this content.
    and the date I think refers to the wrong cell
    Please Login or Register  to view this content.
    i've deleted the "#" error handling there since i think it's not needed any more.

    thanks.

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,824

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Your origin is now
    Please Login or Register  to view this content.
    So
    Please Login or Register  to view this content.
    means you are concatenating Col.H & I.

    Is that what you are tying to do?

    And rest of the code doesn't make sense to me at all.

  21. #21
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    the code is is from post #12. The code refers to the same sheet.

    i just need it to refer to two sheet.. the formula and the data sheet

    and add some range to be copied since it only has 2 range to copy i think this part
    Please Login or Register  to view this content.
    so add another 2 refering to sheet formula to be copied to sheet data.

    Look sample attach in Post#17 for the outline

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

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Variable txt was used to form a unique key based on your original question.

    But now your data changed, so I don't understand which old column refers to column in new data.

    Error value in vba can not be compared to any value, so you will need to escape from it anyway.

    What is the unique key now and which data should be transferred to where?

  23. #23
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    The unique keys will be in Formula sheet Column C6 from value and it will also change depends in the index match formula
    ALG
    BDGE
    #Na
    down

    copy only column F ti I --- if the date in formula sheet is today --date is excluded in the copy paste.

    paste the values in Data sheet if the date is equal to today.

    Column C "dates" in Data sheets remain intact since this will update through index match in formula sheet.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,824

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    I'm still not sure where to paste....
    Result looks doesn't look good.
    Please Login or Register  to view this content.

  25. #25
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    the target to paste the values is in Data D, E,F,G
    C contains the index match for date so this is not included.

    I'll check it.

  26. #26
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    yup. they are in the right cells.

  27. #27
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    Thanks again Jindon. Marking solved now.

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,824

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    I think I'm missed this part
    paste the values in Data sheet if the date is equal to today.
    Please Login or Register  to view this content.

  29. #29
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    hmm but it work when i change the date but to be sure will be using that . btw where is the best location for this code so it updates frequently. worksheet selection change ,worksheet calculate or any suggestion aside from the two?

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    18,824

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    I would place the main code to a Standard module and call it from Worksheet_Activate event, so that it will updates every time Data sheet is selected.

    to Data sheet module:
    Please Login or Register  to view this content.

  31. #31
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,335

    Re: vba overwrite data if 2 condition is met ( date and if helper column does not have err

    ok will do but also i would include it in worksheet open maybe

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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