Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sorted data macro not working. Advice request...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Advice on Working with Routing Slips gabonais New Users to Excel 0 March 19th 08 09:39 PM
Request advice on Split(sFile, "\") dd Excel Programming 0 February 15th 07 05:22 PM
Working days and hours calculation - request assistance please! sbickley Excel Worksheet Functions 0 August 16th 06 06:37 PM
SQL.REQUEST not working in EXCEL 2003 MrSmiley Excel Worksheet Functions 4 April 17th 06 06:32 PM
Need advice and code help with working with *.dbf files in Excel 97 TBA[_2_] Excel Programming 1 September 8th 03 09:14 AM


All times are GMT +1. The time now is 05:48 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement