

December 8, 2016

I have dates in Col B and values in Col C on sheet "Data"
I would like to extract the data in Cols A to C and paste these on sheet "extraction" based on
the values in Col C in the date range containing P2 (Start date) to P3 (end date) and all the values in the date range must equal the value in Q2
Where the values in the date range does not equal the value in Q2, then no data to be extracted
It would be appreciated if someone could kindly assist me


Trusted Members

December 20, 2019

Try the attached
Dates can be a bit moody in Excel access location but i think it should be okay as i am filtering on numbers
Purfleet
Option Explicit
Sub CondFilterNPaste()
Dim sDate As Long
Dim eDate As Long
Dim tValue As Double
Dim fValue As Double
sDate = Range("P2") 'Format(Range("p2"), "DD/MM/YYYY")
eDate = Range("P3") 'Format(Range("p3"), "DD/MM/YYYY")
tValue = Range("q2")
If Worksheets("Data").AutoFilterMode = True Then
Worksheets("Data").AutoFilterMode = False
End If
Range("b:b").TextToColumns Range("b:b"), xlDelimited
Range("A1:c1").AutoFilter field:=2, Criteria1:=">=" & sDate, Operator:=xlAnd, Criteria2:="<=" & eDate
fValue = WorksheetFunction.Sum(Range("c:c").SpecialCells(xlCellTypeVisible))
If Round(fValue, 5) - Round(tValue, 5) <> 0 Then
MsgBox "Filtered rows do not equal " & tValue & " the macro will now exit", vbInformation
Range("a1:c1").AutoFilter
Range("a1:c1").AutoFilter
End
End If
Range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Worksheets("Extraction").Range("a1")
Range("a1:c1").AutoFilter
Range("a1:c1").AutoFilter
End Sub


Trusted Members

December 20, 2019

the 6 highlighted cells are only a small selection of items dated the 8th and the 9th.
I have probably misunderstood the requirements - i thought you wanted to filter on all dates from start date to end date and if the total equals Q2 copy over.
the Macro is filtering on the dates 8th to 9th of Jan which is 11 rows and totals 118211.72 (red text) and is therefore ending.
Purfleet


December 8, 2016

Sorry if I was not very clear
I want to extract the data for the dates contained in P2 to P3 (eg 08/01/2020 to 09/01/2020) where the values (Col c) in the is equal to the value in Q2
Eg if P2 contains 08/01/2020 and P3 contains 01/09/2020 and Q2 is 64982.10 then I need to extract all the items in the date range 08/01/2020 to 09/01/2020 the equals 64982.10. If not nothing to be extracted
Your assistance in resolving this is most appreciated


Trusted Members
Moderators

November 1, 2018



Trusted Members
Moderators

November 1, 2018

Since you essentially need to test every possible combination of values, you'll need a recursive routine such as the one posted a little over halfway down the page here: https://stackoverflow.com/ques.....-given-sum
Alternatively you could simply copy over all the data for the date range and then use Solver to work out which rows meet your target value (if any).


Trusted Members
Moderators

November 1, 2018

Add a column with 1s in it and another column that multiplies the Total Amount column by this new column. Then change N2 to total the multiplication column. Now use Solver to set N3 to 0 by amending the column with the 1s in it, subject to the constraints that those cells must be >=0 and <=1 and integers.
See attached example.

Answers Post
1 Guest(s)
