+ Reply to Thread
Results 1 to 15 of 15

Macro to extract items based on amount

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Macro to extract items based on amount

    I have a sheet called "JNL Items" containing all the data

    I need to extract the items from Sheet "JNL Items" on sheet "Extract Variance" based on the date range and amount in cells U2:W2

    I have manually extracted the data so that you can see what items need to be extracted based on the amount in Cell W2 on sheet “extract variance”

    It would be appreciated if someone could provide me with code to extract all the items pertaining to the amount in Cell W2 on sheet “extract variance



    I have also posted on https://www.myonlinetraininghub.com/...-amount#p21826
    Attached Files Attached Files
    Last edited by Howardc1001; 08-19-2021 at 01:00 PM.

  2. #2
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Macro to extract items based on amount

    First thing I would say is that is you had an Office 365 subscription this is easily done with the new Filter function no need for VBA at all.
    However, since you said your current version is Office 2016 provided below is code that does what your sheet says:
    Please Login or Register  to view this content.
    Unfortunately, that does not return exactly what your demo sheet suggests. That is because you are testing for a balance of <= to 114.75. All of these rows are less than that:

    Reference Source Run A/C Date Debit Credit Balance Narrative Branch
    968257 Nominal Ledger 7/31/2021 0 929.99 -929.99 Fuel BR1 BR1
    968510 Purchase Ordering 7/27/2021 114.75 0 114.75 PO 1401915 REPLACE LOCK BR2
    9683263 Purchase Ordering 7/15/2021 30 0 30 PO 406035 STATIONARY BR2
    9684269 Nominal Ledger 7/31/2021 0 668.2 -668.2 Stationery BR1 BR1
    9684270 Nominal Ledger 7/31/2021 58.75 58.75 Stationery BR1 BR1

    Let me know if I can help you adjust to what you are actually looking for.
    If I was able to help you, you can thank me by clicking the Add Reputation button below and marking the thread Solved.

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Many thanks for the help David


    I only need the data pertaining to the amount of 114.75 to be extracted. If the amount was say 250.95, then all the items summing up to 250.95 to be extracted

    Kindly amend your code accordingly
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Macro to extract items based on amount

    I'm still not totally sure what you are looking for. You are testing against the balance so certainly those negative amounts could be part of the issue. As could those two rows with debits that are less than that amount. Are you maybe looking for all debits or credits whose absolute value is less than or equal to the amount you are testing for?

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Hi David

    Sorry for not being clear. I am looking for all debits or credits whose absolute value is less than or equal to the amount I am testing for

  6. #6
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Macro to extract items based on amount

    Updated code:
    Please Login or Register  to view this content.

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

    Re: Macro to extract items based on amount

    Quote Originally Posted by Howardc1001 View Post
    I need to extract the items from Sheet "JNL Items" on sheet "Extract Variance" based on the date range and amount in cells U2:W2
    U2:W2 is not a valid criteria.
    Use P1:R2, R1 should be either >=, <= or =
    Please Login or Register  to view this content.
    Last edited by jindon; 08-19-2021 at 10:54 PM. Reason: Fixed typo and more

  8. #8
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Thanks for the help Jindon

    The items for the value in R2 are not being extracted

    I have manually extracted the items to show you what should be extracted

    Kindly test and amend your code
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Hi David

    Thanks for the help

    The items for the value in R2 are not being extracted

    I have manually extracted the items to show you what should be extracted

    Kindly test and amend your code
    Attached Files Attached Files

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

    Re: Macro to extract items based on amount

    If you want the "Balance" to result of the Sum, impossible since there would be many combinations, doesn't make sense to me.

  11. #11
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Thanks I thought that may be the case

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

    Re: Macro to extract items based on amount

    Howard...Try this...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 08-20-2021 at 09:08 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!!!

  13. #13
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    Many thank inek

    this is just what I need -your code works perfectly

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

    Re: Macro to extract items based on amount

    Not sure how it will handle with a larger data set but play around with it...Glad I could contribute...Tx for rep +

  15. #15
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Macro to extract items based on amount

    The Data Set will be 50 Items Max

+ 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. Replies: 2
    Last Post: 07-09-2019, 03:16 AM
  2. [SOLVED] Allocating $ amount based on ranked items
    By riordanandy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2017, 05:23 AM
  3. Replies: 5
    Last Post: 06-17-2016, 10:22 AM
  4. [SOLVED] Extract unique items based on criteria
    By YasserKhalil in forum Excel General
    Replies: 4
    Last Post: 09-03-2015, 03:29 AM
  5. Extract unique items based on criteria
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-08-2015, 03:10 PM
  6. [SOLVED] Calculating amount of master items based on mixed amount of items
    By djfscouse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-21-2014, 11:21 AM
  7. [SOLVED] Formula to extract top largest to smallest amount based on 3 criterias
    By aurisab in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-26-2013, 08:05 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