Modification to SUMIF code request

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top