ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sorted data macro not working. Advice request... (https://www.excelbanter.com/excel-programming/426278-sorted-data-macro-not-working-advice-request.html)

joecrabtree
Sorted data macro not working. Advice request...
 
All,

I have the following code, which I want to first sort the data as per
the criteria, and then SUMIF and summarize on an output sheet. The
SUMIF part works fine on its own, and the sort part works fine on its
own. However when I put them together, the SUMIF works on ALL of the
data rather than just the sorted selection. Any ideas where I could be
going wrong?

Thanks in advance for your help,

Regards

Joseph Crabtree

Sub QTY()
With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("U2:U" & LastRow)

End With

Sheets("Data").Select
Rows("1:1").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A20")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 1) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next



With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("AC2:AC" & LastRow)

End With

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Sheets("data").Activate
Range("AC1").Select
Selection.Copy Sheets("output").Range("C20")

Sheets("data").Activate
Range("U1").Select
Selection.Copy Sheets("output").Range("B20")


Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 2) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next


End Sub

joel
Sorted data macro not working. Advice request...
 
You are filtering on Unique values which will reduce the number of rows in
your output. Your SUMIF is using CodeRane and SUmRange which are set prior
to you performing the Filter to get the Unique values. I thinnk you may need
to set the CodeRange and SumRange prior to performing the SUMIF. I didn't
find anyosrt in the code you posted. if you are doing sorting make sure you
don't include any blank cells/rows in your code. The blank cells in the sort
will end up on the bottom of your worksheet that may be causing the problem.

"joecrabtree" wrote:

All,

I have the following code, which I want to first sort the data as per
the criteria, and then SUMIF and summarize on an output sheet. The
SUMIF part works fine on its own, and the sort part works fine on its
own. However when I put them together, the SUMIF works on ALL of the
data rather than just the sorted selection. Any ideas where I could be
going wrong?

Thanks in advance for your help,

Regards

Joseph Crabtree

Sub QTY()
With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("U2:U" & LastRow)

End With

Sheets("Data").Select
Rows("1:1").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A20")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 1) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next



With Sheets("Data")
LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
Set CodeRange = .Range("R2:R" & LastRow)
Set SumRange = .Range("AC2:AC" & LastRow)

End With

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Sheets("data").Activate
Range("AC1").Select
Selection.Copy Sheets("output").Range("C20")

Sheets("data").Activate
Range("U1").Select
Selection.Copy Sheets("output").Range("B20")


Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
CriteriaRange.Offset(0, 2) = Total
Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next


End Sub

joecrabtree
Sorted data macro not working. Advice request...
 
On Mar 31, 2:18*pm, joel wrote:
You are filtering on Unique values which will reduce the number of rows in
your output. *Your SUMIF is using CodeRane and SUmRange which are set prior
to you performing the Filter to get the Unique values. *I thinnk you may need
to set the CodeRange and SumRange prior to performing the SUMIF. *I didn't
find anyosrt in the code you posted. *if you are doing sorting make sure you
don't include any blank cells/rows in your code. *The blank cells in the sort
will end up on the bottom of your worksheet that may be causing the problem.

"joecrabtree" wrote:
All,

I have the following code, which I want to first sort the data as per
the criteria, and then SUMIF and summarize on an output sheet. The
SUMIF part works fine on its own, and the sort part works fine on its
own. However when I put them together, the SUMIF works on ALL of the
data rather than just the sorted selection. Any ideas where I could be
going wrong?

Thanks in advance for your help,

Regards

Joseph Crabtree

Sub QTY()
With Sheets("Data")
* * LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
* * Set CodeRange = .Range("R2:R" & LastRow)
* * Set SumRange = .Range("U2:U" & LastRow)

End With

* *Sheets("Data").Select
* * Rows("1:1").Select
* * Range("K1").Activate
* * Selection.AutoFilter
* * Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A20")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
* * Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
* * CriteriaRange.Offset(0, 1) = Total
* * Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next

With Sheets("Data")
* * LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
* * Set CodeRange = .Range("R2:R" & LastRow)
* * Set SumRange = .Range("AC2:AC" & LastRow)

End With

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Sheets("data").Activate
Range("AC1").Select
Selection.Copy Sheets("output").Range("C20")

Sheets("data").Activate
Range("U1").Select
Selection.Copy Sheets("output").Range("B20")

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
* * Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
* * CriteriaRange.Offset(0, 2) = Total
* * Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next

End Sub

Sorry when I said sort, i really meant filter using the following
code:

Sheets("Data").Select
Rows("1:1").Select
Range("K1").Activate
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

I will try what you have suggested.

Thanks

Joe

joecrabtree
Sorted data macro not working. Advice request...
 
On Mar 31, 3:34*pm, joecrabtree wrote:
On Mar 31, 2:18*pm, joel wrote:



You are filtering on Unique values which will reduce the number of rows in
your output. *Your SUMIF is using CodeRane and SUmRange which are set prior
to you performing the Filter to get the Unique values. *I thinnk you may need
to set the CodeRange and SumRange prior to performing the SUMIF. *I didn't
find anyosrt in the code you posted. *if you are doing sorting make sure you
don't include any blank cells/rows in your code. *The blank cells in the sort
will end up on the bottom of your worksheet that may be causing the problem.

"joecrabtree" wrote:
All,

I have the following code, which I want to first sort the data as per
the criteria, and then SUMIF and summarize on an output sheet. The
SUMIF part works fine on its own, and the sort part works fine on its
own. However when I put them together, the SUMIF works on ALL of the
data rather than just the sorted selection. Any ideas where I could be
going wrong?

Thanks in advance for your help,

Regards

Joseph Crabtree

Sub QTY()
With Sheets("Data")
* * LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
* * Set CodeRange = .Range("R2:R" & LastRow)
* * Set SumRange = .Range("U2:U" & LastRow)

End With

* *Sheets("Data").Select
* * Rows("1:1").Select
* * Range("K1").Activate
* * Selection.AutoFilter
* * Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A20")
ActiveSheet.ShowAllData

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
* * Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
* * CriteriaRange.Offset(0, 1) = Total
* * Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next

With Sheets("Data")
* * LastRow = Sheets("Data").Range("R" & Rows.Count).End(xlUp).Row
* * Set CodeRange = .Range("R2:R" & LastRow)
* * Set SumRange = .Range("AC2:AC" & LastRow)

End With

Sheets("data").Activate
Range("R1", "R" & LastRow).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy Sheets("output").Range("A1")
ActiveSheet.ShowAllData

Sheets("data").Activate
Range("AC1").Select
Selection.Copy Sheets("output").Range("C20")

Sheets("data").Activate
Range("U1").Select
Selection.Copy Sheets("output").Range("B20")

Set CriteriaRange = Sheets("Output").Range("A21")
For r = 2 To Sheets("Output").Range("A21").End(xlDown).Row
* * Total = WorksheetFunction.SumIf(CodeRange, CriteriaRange,
SumRange)
* * CriteriaRange.Offset(0, 2) = Total
* * Set CriteriaRange = CriteriaRange.Offset(1, 0)
Next

End Sub

Sorry when I said sort, i really meant filter using the following
code:

*Sheets("Data").Select
* * Rows("1:1").Select
* * Range("K1").Activate
* * Selection.AutoFilter
* * Selection.AutoFilter Field:=11, Criteria1:="Kovácsolás"

I will try what you have suggested.

Thanks

Joe

Im still not having any luck. Basically the macro SUMIFs all the data
still. It appears that the filter is working initilally, but then
someway through the code it is taken off, and the SUMIF function is
applied to ALL rows.

Any more ideas?

Thanks

Joe


All times are GMT +1. The time now is 12:40 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com