## vba formula vs calculation - problem with "<=" or dates

### vba formula vs calculation - problem with "<=" or dates

This formula works in a cell:
=SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

1) I want to have vba make this calculation - because calculating it in
each cell bogs down the xls.
I got the following vba to work but I cant figure out how to add "<=" to
the last clause (col N has dates):

.Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
",N\$2:N\$26196," & "n" & i & ")"

2) How do I get VBA to make this calculation and put the result in each
cell instead of putting a formula in each cell?

...I am open to other suggestions... e.g. would an array use less resources?

Thanks.

### vba formula vs calculation - problem with "<=" or dates

Dear Mark,

You can try..
.
.Range("q" & i).Formula = *Evaluate(*"=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," &
"a" & i & ",N\$2:N\$26196," & "n" & i & ")"*)*

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

> This formula works in a cell:
> =SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

> 1) I want to have vba make this calculation - because calculating it in
> each cell bogs down the xls.
> I got the following vba to work but I cant figure out how to add "<=" to
> the last clause (col N has dates):

> .Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
> ",N\$2:N\$26196," & "n" & i & ")"

> 2) How do I get VBA to make this calculation and put the result in each
> cell instead of putting a formula in each cell?

> ...I am open to other suggestions... e.g. would an array use less
> resources?

> Thanks.

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

### vba formula vs calculation - problem with "<=" or dates

Thank you Noorain.
Yes, Evaluate() does put a value in the cell instead of a formuala. That is
great.
Do you have any idea how to make the equation include "<=" in the last
clause?

> Dear Mark,

> You can try..
> .
> .Range("q" & i).Formula = *Evaluate(*"=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," &
> "a" & i & ",N\$2:N\$26196," & "n" & i & ")"*)*

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>> This formula works in a cell:
>> =SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

>> 1) I want to have vba make this calculation - because calculating it in
>> each cell bogs down the xls.
>> I got the following vba to work but I cant figure out how to add "<=" to
>> the last clause (col N has dates):

>> .Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
>> ",N\$2:N\$26196," & "n" & i & ")"

>> 2) How do I get VBA to make this calculation and put the result in each
>> cell instead of putting a formula in each cell?

>> ...I am open to other suggestions... e.g. would an array use less
>> resources?

>> Thanks.

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

> Dear Mark,

> You can try..
> .
> .Range("q" & i).Formula = *Evaluate(*"=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," &
> "a" & i & ",N\$2:N\$26196," & "n" & i & ")"*)*

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>> This formula works in a cell:
>> =SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

>> 1) I want to have vba make this calculation - because calculating it in
>> each cell bogs down the xls.
>> I got the following vba to work but I cant figure out how to add "<=" to
>> the last clause (col N has dates):

>> .Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
>> ",N\$2:N\$26196," & "n" & i & ")"

>> 2) How do I get VBA to make this calculation and put the result in each
>> cell instead of putting a formula in each cell?

>> ...I am open to other suggestions... e.g. would an array use less
>> resources?

>> Thanks.

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

> Dear Mark,

> You can try..
> .
> .Range("q" & i).Formula = *Evaluate(*"=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," &
> "a" & i & ",N\$2:N\$26196," & "n" & i & ")"*)*

> --
> Thanks & regards,
> Noorain Ansari
> www.noorainansari.com
> www.excelmacroworld.blogspot.com

>> This formula works in a cell:
>> =SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

>> 1) I want to have vba make this calculation - because calculating it in
>> each cell bogs down the xls.
>> I got the following vba to work but I cant figure out how to add "<=" to
>> the last clause (col N has dates):

>> .Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
>> ",N\$2:N\$26196," & "n" & i & ")"

>> 2) How do I get VBA to make this calculation and put the result in each
>> cell instead of putting a formula in each cell?

>> ...I am open to other suggestions... e.g. would an array use less
>> resources?

>> Thanks.

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

### vba formula vs calculation - problem with "<=" or dates

There may? be a better way to do this. Reply to ME at the address below with your file.

Don Guillett
Microsoft MVP Excel
SalesAid Software

From: Mark Kerin
Sent: Friday, June 01, 2012 1:43 PM

Subject: \$\$Excel-Macros\$\$ vba formula vs calculation - problem with "<=" or dates

This formula works in a cell:
=SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

1) I want to have vba make this calculation - because calculating it in each cell bogs down the xls.
I got the following vba to work but I cant figure out how to add "<=" to the last clause (col N has dates):

.Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i & ",N\$2:N\$26196," & "n" & i & ")"

2) How do I get VBA to make this calculation and put the result in each cell instead of putting a formula in each cell?

...I am open to other suggestions... e.g. would an array use less resources?

Thanks.
--
FORUM RULES (986+ members already BANNED for violation)

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.

------------------------------------------------------------------------------------------------------

### vba formula vs calculation - problem with "<=" or dates

Dear Mark,

>   There may? be a better way to do this. Reply to ME at the address below

> Don Guillett
> Microsoft MVP Excel
> SalesAid Software

> *Sent:* Friday, June 01, 2012 1:43 PM

> *Subject:* \$\$Excel-Macros\$\$ vba formula vs calculation - problem with
> "<=" or dates

> This formula works in a cell:
> =SUMIFS(O\$2:O\$26196,A\$2:A\$26196,A2,N\$2:N\$26196,"<="&N2)

> 1) I want to have vba make this calculation - because calculating it in
> each cell bogs down the xls.
> I got the following vba to work but I cant figure out how to add "<=" to
> the last clause (col N has dates):

> .Range("q" & i).Formula = "=SUMIFS(O\$2:O\$26196,A\$2:A\$26196," & "a" & i &
> ",N\$2:N\$26196," & "n" & i & ")"

> 2) How do I get VBA to make this calculation and put the result in each
> cell instead of putting a formula in each cell?

> ...I am open to other suggestions... e.g. would an array use less
> resources?

> Thanks.
> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

--
Thanks & regards,
Noorain Ansari
www.noorainansari.com
www.excelmacroworld.blogspot.com

Please explain the differences between ports; parallel? usb? serial?

--
Posted via CNET Help.com
http://www.help.com/