How to write a VBA code to select certain rows based on certain conditions

Use something like

var1 = WorksheetFunction.SUMIF(range1, value, Range2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

<houstoncity2004@yahoo.com> wrote in message
news:e570d4ce.0406240950.1cc91792@posting.google.com...

I need help to write part of a VBA code in Excel. I am trying to do
calculations on rows that meeting certain requirements, but I do not
know how to established that in VBA. I know that if I use the
worksheet itself, I can do something like SUMIF() function. But, when
I put the same function in the VBA editor, it returns error. A simple
version of my data:

Group No Member No Amount spent
1 1 10
1 2 3
1 3 4
2 1 6
2 2 1
3 1 12
4 1 3
4 2 1

I am trying to calculate the total amount spent based on the group no.
FOr example, how much does group no.1 spent, how much does group no.2
spent, etc.

I appreciate anybody who can help me with this. Thank you very much.

Hi houstoncity2004,


Try this :

'------------------
Sub SommeIF()

With Worksheets("Sheet1")
Group1 = Application.SumIf(.Range("A1:A10"), "=" & 1, .Range("B1:B10"))
Group2 = Application.SumIf(.Range("A1:A10"), "=" & 2, .Range("B1:B10"))
End With

End Sub
'------------------


Salutations!




<houstoncity2004@yahoo.com> a écrit dans le message de news:e570d4ce.0406240950.1cc91792@posting.google.com...
I need help to write part of a VBA code in Excel. I am trying to do
calculations on rows that meeting certain requirements, but I do not
know how to established that in VBA. I know that if I use the
worksheet itself, I can do something like SUMIF() function. But, when
I put the same function in the VBA editor, it returns error. A simple
version of my data:

Group No Member No Amount spent
1 1 10
1 2 3
1 3 4
2 1 6
2 2 1
3 1 12
4 1 3
4 2 1

I am trying to calculate the total amount spent based on the group no.
FOr example, how much does group no.1 spent, how much does group no.2
spent, etc.

I appreciate anybody who can help me with this. Thank you very much.

First, while I assume you have a reason for wanting to do this in VBA,
I'd feel remiss if I didn't suggest you look at Pivot Tables instead (see

http://peltiertech.com/Excel/Pivots/pivotstart.htm

for an intro).

You can use the SUMIF function in VBA:

Dim dResult As Double
dResult = Application.SumIf(Range("A1:A10"), "=1", Range("C1:C10"))



In article <e570d4ce.0406240950.1cc91792@posting.google.com>,
houstoncity2004@yahoo.com (houstoncity2004@yahoo.com) wrote:

I need help to write part of a VBA code in Excel. I am trying to do
calculations on rows that meeting certain requirements, but I do not
know how to established that in VBA. I know that if I use the
worksheet itself, I can do something like SUMIF() function. But, when
I put the same function in the VBA editor, it returns error. A simple
version of my data:

Group No Member No Amount spent
1 1 10
1 2 3
1 3 4
2 1 6
2 2 1
3 1 12
4 1 3
4 2 1

I am trying to calculate the total amount spent based on the group no.
FOr example, how much does group no.1 spent, how much does group no.2
spent, etc.

I appreciate anybody who can help me with this. Thank you very much.

Hi houstoncity2004 ;-))
as i understand the simple version of your data :
you can use something like this :

Sub Macro1()
' Macro enregistrée le 24/06/2004 par GeeDee
Range("A2:A100").Name = "Group_no"
Range("C2:C100").Name = "Amount_spent"
'----a loop may be better used for large number of Group No----
Range("G1").Name = "Group1"
Range("G2").Name = "Group2"
Range("G3").Name = "Group3"
Range("G4").Name = "Group4"
Range("Group1").Formula = "=SUMPRODUCT(Amount_spent*(Group_no=1))"
Range("Group2").Formula = "=SUMPRODUCT(Amount_spent*(Group_no=2))"
Range("Group3").Formula = "=SUMPRODUCT(Amount_spent*(Group_no=3))"
Range("Group4").Formula = "=SUMPRODUCT(Amount_spent*(Group_no=4))"
'-----------
End Sub

@+ Modeste(®) GeeDee
HTH
Much more tips and tricks on the best "Excel Addicts" Site in french
language
http://www.excelabo.net