Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len

Hi Len

If Acct has been defined and is in column A, then range for column C would
be
Offset(Acct, 0, 2)
and for column D would be
Offset(Acct, 0, 3)

SUMIF(Acct, "Total*", Offset(Acct, 0, 2))-SUMIF(Acct,
"Expenses*",Offset(Acct, 0, 3))*-2

--
Regards
Roger Govier

"Len" wrote in message
...
Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len

__________ Information from ESET Smart Security, version of virus
signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com

LastRow = Cells(Rows.Count, "A")End(xlUp).Row

Range(Range("A2"), Cells(LastRow, "A")).Name = "Acct"

HTH

Bob

"Len" wrote in message
...
Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len

That should be a dot (.)

Bob

"Bob Phillips" wrote in message
...
LastRow = Cells(Rows.Count, "A")End(xlUp).Row

Range(Range("A2"), Cells(LastRow, "A")).Name = "Acct"

HTH

Bob

"Len" wrote in message
...
Hi,

In excel vba, how to set dynamic named range so that it can use in
sumif excel formula

E.g.

=SUMIF(Acct, "Total*", xxx)-SUMIF(Acct, "Expenses*",yyy)*-2

Acct refer to named range in column A
xxx refer to dynamic named range in column C
yyy refer to dynamic named range in column D

Can anyone help and thanks in advance

Regards
Len

Hi Roger, Bob

Thanks a lot your formula and both work great!.......



Best Regards
Len