PDA

View Full Version : Using Sumif in a Macro



Jason_Buda
03-27-2009, 02:45 PM
I am trying to write a macro to sum the information below onto another page based upon the "pre contract condition". Meaning i want to sum all of "base contract" into one row with the each of the following columns summed so that in the end it appears on the new sheet like the bottom column. My problem is that besides "base contract" the contract condition entries are not static. My question is how do i do a "sumif" with the data based upon an undefined number of conditions and returned the summed result? I have not written a macro like this before and am not sure where to start. (data is attached)

mdmackillop
03-27-2009, 04:06 PM
Hi Jason,
Welcome to VBAX

Give this a try

Sub Macro1()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim Rng As Range

Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
Set Rng = Range(sh1.Cells(1, 1), sh1.Cells(Rows.Count, 1).End(xlUp))

sh1.Range("A1:G1").Copy sh2.Range("A1")
Rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=sh2.Range("A1"), Unique:=True
Set Rng = Range(sh2.Cells(2, 1), sh2.Cells(Rows.Count, 1).End(xlUp))
Rng.Offset(, 1).Resize(, 6).FormulaR1C1 = "=SUMIF(Sheet1!C1,Sheet2!RC1,Sheet1!C)"
End Sub

Jason_Buda
03-30-2009, 02:29 PM
Thanks for the response. I have copied this into my macro, but I am now getting an error message that says: "This command requires at least two rows of source data. You cannot use this command on a selection in only one row." Can you help me resolve this? Thanks for your help

Bob Phillips
03-30-2009, 03:53 PM
Do you have any data other than headings? It works on your workbook supplied.