J
joecrabtree
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
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