VBA to compare to 2 cells and copy cell value to next empty row in sheet

  • Hello


    i wonder if anybody can help me. i am putting together a sheet that will hold a large amount of raw data. from that data i would like to filter results and copy certain cell references to the next empty row on another sheet.


    I have been able to put together some code that will search down column D on my ‘Raw Data’ Sheet and if the word Missed is found it will copy Cells A and D onto another sheet – ‘Instances, starting in cells A2 and B2 and will copy into the next empty row.


    What I would like to do is either modify what I have or even re-write and have two separate macros. So that it in addition to that search it also searches column D for the word ‘Busy’ and any instance in Column I where the duration is above 00:04:00 and then copy those results to the next empty row on my ‘Instances Sheet’ I have attached a sample Workbook.


    I have also attached my code but I am not sure on how to modify what I have to search for two values in two separate columns


    Thank you in advance for any help you can provide.


    Files

    • Sample.xlsm

      (21.76 kB, downloaded 47 times, last: )
  • Hello,


    For the kind of problem you are exposing ... you should avoid using a Loop ... since AutoFilter is much more effective ...:)


    Now ... not sure to understand what are you expected results ... could you clarify what the final outcome should look like ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi thank you for your reply.


    i tried recording a macro that would apply the filter i wanted to my data but when it came to copying it to the new sheet when copying it reference the first cell from the filter not from A2 which is where the filtered data would start from (sorry i know thats confusing)


    i have attached a new workbook with an example. On the 'Instances' Sheet you can see where it has filtered the initial results based on the term 'Missed' in column D (Which i have the macro for) but then i want to run another filter on the same data for any instances of the term 'Busy' in Column d AND where the Duration is above 00:04:00 in column I, and if those criteria are met put the value of A and D from the same row on the next empty row of the Instances sheet'

    Files

  • So i think i have been a massive idiot, the main reason i wanted this was to copy the filtered results based on two criteria to the next empty row on another sheet, but i already had a macro that did that so i recorded a macro of myself applying the filter and pasting the data onto the other sheet THEN applying my existing macro which puts the data into the next available cell. so this issue is now resolved and i am sorry to have wasted time.


    Stay safe!

  • Quite honestly ... it is rather confusing ...


    Is the suggested proposal in Column J ... of any help ... in combining your filtering criteria ...?


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macro ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)