Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
simplify SUMIF stmts
I have to code a formula in vba.. it is below:
If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23), (SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25)) I need to simplify this because I am going to be putting the formula in VBA. I just don't know how to handle the many SumIf statements. Any advice would be great. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
simplify SUMIF stmts
It doesn't look as if you can simplify it if you want to maintain the IF
test, but why do you need to put it in VBA, and what difficulties are you envisaging? Will you write the formula to a cell within VBA, or evaluate the formula? -- HTH RP (remove nothere from the email address if mailing direct) "Monique" wrote in message ... I have to code a formula in vba.. it is below: If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23), (SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25)) I need to simplify this because I am going to be putting the formula in VBA. I just don't know how to handle the many SumIf statements. Any advice would be great. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
simplify SUMIF stmts
If I can get rid of any part of it, I would. I have to put it in VBA because
I'm making a calculation engine in the background. This way users will not be able to change the formulas, etc etc. I am going to be writing the formula to a range of cells in excel.. using the range("xx").resize(etc etc)... in vba. I am just trying to figure out the most efficient way to get this formula coded? "Bob Phillips" wrote: It doesn't look as if you can simplify it if you want to maintain the IF test, but why do you need to put it in VBA, and what difficulties are you envisaging? Will you write the formula to a cell within VBA, or evaluate the formula? -- HTH RP (remove nothere from the email address if mailing direct) "Monique" wrote in message ... I have to code a formula in vba.. it is below: If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23), (SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25)) I need to simplify this because I am going to be putting the formula in VBA. I just don't know how to handle the many SumIf statements. Any advice would be great. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
simplify SUMIF stmts
Hi Monique,
If your formula is good, try this : you just have to put your formula into brackets on the same line and you will get your result in your variable. Dim MyVar As Double MyVar = [If(SumIf(F10:AJ10, F10, F23:AJ23)*D100,SumIF(F10:AJ10, F10, F23:AJ23),(SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25))] Salutations! "Monique" a écrit dans le message de news: ... I have to code a formula in vba.. it is below: If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23), (SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25)) I need to simplify this because I am going to be putting the formula in VBA. I just don't know how to handle the many SumIf statements. Any advice would be great. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
simplify SUMIF stmts
So do you just want to do
Range("A1").Formula="If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23),(SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25))" Range("A1").Autofill Range("A1:A100") -- HTH RP (remove nothere from the email address if mailing direct) "Monique" wrote in message ... If I can get rid of any part of it, I would. I have to put it in VBA because I'm making a calculation engine in the background. This way users will not be able to change the formulas, etc etc. I am going to be writing the formula to a range of cells in excel.. using the range("xx").resize(etc etc)... in vba. I am just trying to figure out the most efficient way to get this formula coded? "Bob Phillips" wrote: It doesn't look as if you can simplify it if you want to maintain the IF test, but why do you need to put it in VBA, and what difficulties are you envisaging? Will you write the formula to a cell within VBA, or evaluate the formula? -- HTH RP (remove nothere from the email address if mailing direct) "Monique" wrote in message ... I have to code a formula in vba.. it is below: If(SumIf(F10:AJ10, F10, F23:AJ23)*D100, SumIF(F10:AJ10, F10, F23:AJ23), (SumIF(F10:AJ10, F10, F25:AJ25)/SumIF(F10:AJ10, F10, B57:AF57))*(C25/K25)) I need to simplify this because I am going to be putting the formula in VBA. I just don't know how to handle the many SumIf statements. Any advice would be great. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple If Stmts | Excel Discussion (Misc queries) | |||
Nested 'If" stmts | Excel Discussion (Misc queries) | |||
Creating macros with if stmts | Excel Discussion (Misc queries) | |||
Monthly bank stmts | Excel Worksheet Functions | |||
IF Statement that's conditional on multiple IF Stmts in other cell | Excel Worksheet Functions |