+ Reply to Thread
Results 1 to 31 of 31

Macro for selecting employees that have an amount > 1000 this month w.r.t. previous month

  1. #1
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Macro for selecting employees that have an amount > 1000 this month w.r.t. previous month

    Hi,

    I would like to create a macro that selects the employeename and amount difference for each employeenumber which has an amount in period 20XX-XX that is >1000 than in period 20XX-XX -1. So for example if the amount in 2020-03 is more than 1000 larger than the amount for that employee in period 2020-02. The employeenumber and the amountdifference should then be copied to a row in column F.

    I guess i should use an algorithm that, for each employeenumber, searches the amount, than searches for that employeenumber in period 20XX-XX+1 and than for the amount in that row. than it should calculate if the difference of these two amounts > 1000. If so, the employee number and the difference should be selected and copied to row F.

    I wrote variables so that the first and last rows for which this algorythm should search is determined. However, i don't have an idea how to create this algorithm. I have been trying for hours but i can't make it happen. Is there a way to do this?

    Any input is very much appreciated!


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Hi,

    I have added some code.
    The problem now is that Amount1 that is nesessary for calculating if the difference > 1000 is changed for every i.

    Please Login or Register  to view this content.
    b

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Maybe something like this ?
    (different way than yours)

    Please Login or Register  to view this content.
    The code use a function to collect unique value of the Employee Number,
    and also the code use temporary columns (column NTL) to sort the data in two level, first by Employee Number - second by Date.
    Before the end of the sub, the temporary columns is cleared.
    Last edited by karmapala; 06-17-2020 at 05:19 AM.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    My take on this one...Snippets of code taken from jindon
    Please Login or Register  to view this content.
    Last edited by sintek; 06-17-2020 at 08:21 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    @sintek,

    I'm always amazed with the kind of code like yours.
    But no matter how hard I try to understand it,
    I still can't grasp how the code works .

    Anyway I'm glad to have more knowledge about VBA from your code.
    Thank you sintek .

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Just like you...I am also learning from the Forum...😉

  7. #7
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Wow! Thank you, both work very well.
    For simplification i changed the column letters in my question. In my real life problem columns B and C should become Columns C and H.
    Column F were the results are shown should become column A in another worksheet. Is it possible to show me the code for this situation?
    I tried it myself but it didn't work as i am still in the beginning of my vba coding life.

    Thank you very much!

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    For simplification i changed the column letters in my question
    Hi MaartenRo

    For future reference...It is very important that your sample files have the exact same setup as your original file...
    That way, if you do not know how to implement code and changes thereto, we do not waste our time on supplying incorrect code from the get go...

    So...Upload a sample file depicting your actual setup so that we can amend...

  9. #9
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    As Mr.Sintek say ... it's better to upload the sample workbook to show how is the model of your data.

    Anyway....
    Maybe try this :

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Ok thank you for your advice and for your input. I have the excel sheet and the problem is the same except that the columns are different and i added a name column with names of the employees. I would like to have the numbers, names, and amount differences of sheets "tot code 88" shown in column "k" of sheet "Info88" and for "tot code 89" shown in column K of sheet "info89".
    Thank you very much!
    Attached Files Attached Files

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Delete Empty Column A of Sheets("Tot Code 89")...Why it is not same as 88...have no idea...
    Then this will work...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Please have a look at the attachment.

    Your sheet TOT CODE 89 data placement is different than sheet TOT CODE 88.

    Sheet TotCode88, the EmpNum is in column A
    Sheet TotCode89, the EmpNum is in column B

    Sheet TotCode88, the Period is in column H
    Sheet TotCode89, the Period is in column M

    And I'm still not clear on how do you want the result, because it maybe...

    A :
    Sheet Info88, col A is EmpNum, col B is Name, col C is Amount, col K is the difference
    Sheet Info89, col B is EmpNum, col C is Name, col D is Amount, col K is the difference

    or B:
    Sheet Info88, col K is EmpNum, col L is Name, col M is Amount, col N is the difference
    Sheet Info89, col K is EmpNum, col L is Name, col M is Amount, col N is the difference

    My attachment workbook put the result like B.
    And the code is based on the data seen in TOT CODE 88 sheet and TOT CODE 89 sheet of your sample workbook.
    If you change the data column position, for example :
    on sheet TOT CODE 89 you move the Period from column M to column H, you need to change the variable in the code.
    on sheet TOT CODE 88 you move the Period from column H to column M, you need to change the variable in the code.

    Also, your TOT CODE 89 period is all the same.
    Example case:
    4 d 5000 (data 1)
    4 d 20 (data 2)
    4 d 4000 (data 3)

    Because it all in the same period,
    the code write the result where data 2 to data 3 difference is 3980.
    Attached Files Attached Files
    Last edited by karmapala; 06-18-2020 at 09:09 AM.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Only now noticed you want 3 column output
    Change this snippet
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    And replace this part
    Please Login or Register  to view this content.
    With
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Thank you both very much! The setup should exactly be as Karmapala stated in B. I changed data because of privacy sensitivety therefore the numbers on both sheets are the same. I tried the code with the real life list wich is very long and it worked.

    Is it also possible to highlight the rows in the "tot code 88" and "tot code 89" sheets with the data that are shown in the tabs "info 88" and "info 89"?

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Is it also possible to highlight the rows in the "tot code 88" and "tot code 89" sheets
    Do you mean the row that contains the max value if the criteria is met?

  16. #16
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    yes exactly

  17. #17
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Quote Originally Posted by MaartenRo View Post
    yes exactly
    Sorry I don't quite understand about you want to highlight that contains the max value if the criteria is met.

    Nr --- Name --- Amount --- Period
    1 ----- a ----- 100 ----- 2020-01
    1 ----- a ----- 1500 ---- 2020-02
    1 ----- a ----- 200 ----- 2020-03

    Above is after the data is sorted.
    you want the second row from the list above is written to sheet info88 column K :
    1 .... a .... 1500 ... 1400

    (my code is wrong because I have the code to write the first row, but I already fixed it with the code below)

    Now, what if the data (after sorted) is like this :
    Nr --- Name --- Amount --- Period
    1 ----- a ----- 100 ----- 2020-01
    1 ----- a ----- 1500 ---- 2020-02
    1 ----- a ----- 4000 ----- 2020-03

    Quote Originally Posted by MaartenRo View Post
    Hi,
    I would like to create a macro that selects the employeename and amount difference for each employeenumber
    which has an amount in period 20XX-XX that is >1000 than in period 20XX-XX -1.
    So it is written to sheet info88 column K :
    1 .... a .... 1500 ... 1400
    1 .... a .... 4000... 2500

    Now about highlighted sheet TOT CODE:
    Do you want EmpNum 1 in sheet TOT CODE 88, highlighted where (A) the row for EmpNum 1 has amount value 4000 only (the max value)?
    or do you want to (B) also highlight the row for EmpNum 1 which has amount value 1500 ?

    Anyway, the code below is point-(B), because you said :
    Quote Originally Posted by MaartenRo View Post
    Is it also possible to highlight the rows in the "tot code 88" and "tot code 89" sheets
    with the data that are shown in the tabs "info 88" and "info 89"?


    Please Login or Register  to view this content.
    Remove all the code lines between the If Nxt - Cur > 1000 Then and the End If then paste the code above (or just download the attachment).
    Attached Files Attached Files
    Last edited by karmapala; 06-19-2020 at 07:27 AM.

  18. #18
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Thank you very much for the input. In order to be more specific in the answers of these questions i need to study the macro's more.
    I tried one macro and it worked!

  19. #19
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Hi

    Thank you very much for your code. Unfortunately i get an error message when i try to run your code on the sheet that i actually use:
    error 13 types do not match. The following part of Karmala's code is highlighted in yellow:


    Please Login or Register  to view this content.

    I tried your code for the uploaded file (the one that i simplified for privacy reasons) and then it works perfectly. I don't understand why i get the error message on the sheet that i use. My sheet differs from the one i uploaded in that it has has more than 1000's of rows instead of 15 in the uploaded file.

    Do you happen to know how to solbve this problem so that the code can be fully executed? Unfortunately my file that i actually use is too large so i can't upload it (i also can't use winzip to package it on my work computer).
    Help is much appreciated!
    Last edited by MaartenRo; 06-29-2020 at 04:33 AM.

  20. #20
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    I have not yet received feedback and so much time lapses between responses...so...Not going to worry much...

    You are referencing Karmapala's code...
    Attached Files Attached Files

  21. #21
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Hi MaartenRio,

    To be honest, it's difficult for me to know why it throw an error in that line.

    For the time being, if I may suggest you, please do :
    1. Copy your original workbook (with the macro already there).
    2. In the copied workbook, delete your data in sheet TOT CODE 88 to around just 200 rows left, the same with sheet TOT CODE 89.
    3. Run the macro.

    If it's still give you an error at the same line,
    give a comment sign ' in front of the line which throw you an error like below:
    Please Login or Register  to view this content.
    Then run the code again.
    If it runs without error and it still gives you the expecting result (except the highlighting state),
    then we know that the trouble is only at the code line above.

    Next, if you don't mind, please upload of that your copied workbook which already has a reduced data,
    I hope I can find what is the cause that line throw an error after looking the original data in your copied workbook.

    Thank you.
    Last edited by karmapala; 06-29-2020 at 05:04 AM.

  22. #22
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Thank you. I did as you suggested and there is no error if i leave out the lines beginning with ' (that were the cause for the error).

  23. #23
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    MaartenRo,

    LOL, very sorry I can't find why it throw an error at a certain Naam CODE 88.

    Anyway, please keep that trouble line commented with ' (or you can just remove it)
    Please Login or Register  to view this content.
    then add this line right below it:
    Please Login or Register  to view this content.

    My "trouble" code before is using "match" which to be honest it's a kind of new code to me - so it's difficult for me to track the result.
    Now I change the code using "find" as it's easier for me to track the result.

    I hope the highlighting code now won't throw an error.

    BTW, sheet TOT CODE 89 won't give you the expected result, because all the values under JAAR/MND column is the same.

    In the attachment, the test sub is in module2.
    Last edited by karmapala; 06-29-2020 at 07:51 AM.

  24. #24
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Thank you! It works. Is it also possible to colour only the first three cells in the row instead of the whole row?
    I tried to change your code but it won't work

    Please Login or Register  to view this content.

  25. #25
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Quote Originally Posted by MaartenRo View Post
    Is it also possible to colour only the first three cells in the row instead of the whole row?
    Please play around in this line :

    Please Login or Register  to view this content.
    at the ofst2 + 1,
    keep the ofst2, play around with +1 to see the result.

    For example,
    change the + 1 to -2, then see the result when F8 (step-run) in the vba editor.

    Or you can just entirely ignore/remove the ofst2, but then don't put negative value.
    Example : d.Offset(0, 3) ... d.Offset(0,2), and so on.
    Don't : d.offset(0,-1) ... d.offset(0,-2), and so on.
    Last edited by karmapala; 06-30-2020 at 04:15 AM.

  26. #26
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Thank you, that works very well

  27. #27
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    You're welcome, MaartenRo.
    Glad I can help

  28. #28
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Hi,

    There's another adaptation that i would like to make in the code. As an unexperienced vba coder, i just figured out it's probably better to simply colour the relevant cells (like your code perfectly does) without copying it to another sheet. I now have the skills to simply copy the table to another sheet and make a sortation based on color of the cells.
    Do you know what part of the code can be left out then?

    Thanks in advanced!

  29. #29
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Quote Originally Posted by MaartenRo View Post
    Hi, it's probably better to simply colour the relevant cells (like your code perfectly does) without copying it to another sheet.
    If you mean that now you don't want the sheet Info88 (and Info89) anymore,
    because now you just want to highlight the cell which the value is > 1000 then the value of the month before,
    then please remove the below codes ..... or in case you change your mind back to what you want before, just put a comment ' sign... like this :

    Please Login or Register  to view this content.
    Please remember that now you don't know the difference value,
    before you can see it in sheet Info, because there is a line in the code to put the difference value on sheet Info88 or info89.

    Just tell me if you still want to have sheet Info with only the different value.

    I now have the skills to simply copy the table to another sheet and make a sortation based on color of the cells.
    Since my code is using tmp range for sorting, there is another option which is :
    copying first the whole data in sheet TOT CODE to a new sheet,
    do the highlighting process based on my code in this new sheet,
    then finally sort it based on color of the cells in this new sheet.

    The original data on TOT CODE sheet won't have the highlighted cell.

    And btw, please respond to Mr. sintek as he has already provided you another code option,
    so you might get a much better and cleaner code from him.
    Last edited by karmapala; 07-01-2020 at 05:48 AM.

  30. #30
    Forum Contributor
    Join Date
    02-27-2020
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    425

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Hi,

    Thank you. I would like to have the display value displayed indeed if possible. But i think it is better to have the relevant rows coloured in the tot code sheet and subsequently i can copy it
    to the info sheet. It would be great if another column could be added to the info sheet with the difference value as this information is very useful.

    If i run your code i get an error message which i don't understand: error 9 subscript out of range. I didn't change the sheet names.

  31. #31
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Macro for selecting employees that have an amount > 1000 this month w.r.t. previous mo

    Quote Originally Posted by MaartenRo View Post
    Hi,

    Thank you. I would like to have the display value displayed indeed if possible.
    But i think it is better to have the relevant rows coloured in the tot code sheet and subsequently i can copy it
    to the info sheet. It would be great if another column could be added to the info sheet with the difference value as this information is very useful.

    If i run your code i get an error message which i don't understand: error 9 subscript out of range. I didn't change the sheet names.
    Please see the attachment, module2.
    The test2 sub will just give a highlighting result on the original sheet (TOT CODE).
    There will be no value difference information in this sub test2.
    In this sub, there's no need a blank sheet with a name "Info".

    The test3 sub will copy the original sheet (TOT CODE) to a new sheet,
    highlight the row in this new sheet, put the difference value on the second column after the last column,
    then sort the data in order the highlighted rows are on the top.
    The original data in sheet TOT CODE remains intact.
    In this sub, also there's no need a blank sheet with a name "Info".

+ 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] Need formula to calculate days used in month initiated in previous and selected month
    By Ochimus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-14-2021, 05:54 AM
  2. Replies: 1
    Last Post: 07-06-2015, 04:43 PM
  3. [SOLVED] Formula for amount on previous month based on current month
    By Yu Marquez in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-21-2014, 12:42 AM
  4. Macro using vlookups comparing 2 months paysheet(previous month and current month)
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-07-2012, 03:45 AM
  5. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  6. [SOLVED] VBA to identify the current month and previous month based on system date
    By ravikumar00008 in forum Excel General
    Replies: 10
    Last Post: 07-26-2012, 10:04 AM
  7. Replies: 4
    Last Post: 03-19-2012, 08:58 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