Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.fr.excel
|
|||
|
|||
How to write a VBA code to select certain rows based on certain conditions
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.fr.excel
|
|||
|
|||
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) wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.fr.excel
|
|||
|
|||
How to write a VBA code to select certain rows based on certain conditions
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! a écrit dans le message de om... 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. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc,microsoft.public.fr.excel
|
|||
|
|||
How to write a VBA code to select certain rows based on certain conditions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Write a macro that hides rows based on empty cells | Excel Discussion (Misc queries) | |||
VBA code to select rows | Excel Discussion (Misc queries) | |||
+/- in sheet2 based on conditions and dynamic rows in sheet1 | Excel Discussion (Misc queries) | |||
Conditionally summing cells based on conditions in other rows | Excel Worksheet Functions | |||
How do I write a formula to color code based on dates provided or. | Excel Worksheet Functions |