Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modification to SUMIF code request
All,
I have the following code. Currently the code runs using a SUM IF function. However this works for basically everything in column R (code range) that is a unique value and sums it. There is another column in the spreadsheet (Column Q). This has another set of values in it. I would like to modify this macro to work only if column Q contains certain data which would be defined in the macro. For exammple if column Q contains the word 'forge' then the macro will run only on the rows that have 'forge in them. Some usefull advice has been given on using the sum product furnction instead of the SUMIF function, but I still cant get it to work. Any advice you may have would be greatly appreciated. Regards Joseph Crabtree ........................................ Sub MasterSummarySheet() 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").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 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Data Code Modification Request | Excel Programming | |||
Code modification help | Excel Worksheet Functions | |||
Help 2nd request (sumif formula of similar 17-04-08) | Excel Discussion (Misc queries) | |||
Code modification help please | Excel Programming | |||
Modification in the CODE to HIDE rows and columns that start with ZERO (code given) | Excel Programming |