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

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.

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?

Updated code:

Please Login or Register  to view this content.

Originally Posted by Howardc1001

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.

Howard...Try this...

Please Login or Register  to view this content.

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