PDA

View Full Version : Incorporating lastrow variables into sumproduct formula



JZB
06-08-2009, 10:36 AM
hi guys

i have wirtten a sum product formula

=SUMPRODUCT(('Man balances'!AH2:AH7)*('Man balances'!C2:C7=$A$115)*('Man balances'!AI2:AI7='Cash Balances'!I1))

the only thing require to make this repeatable in a macro is to change the cell criteria in the man balances tab (C2:C7) to C2:nlastrow :dunno

Can anyone help me fudge the formula?

Thanks

mdmackillop
06-08-2009, 10:46 AM
Are you wanting the macro to write the adjusted formula to your worksheet?

JZB
06-09-2009, 12:48 AM
correct.

basically the man balances data will change day on day. so today the last row is 7. however could be 100 tomorrow. so just need to adjust the sumproduct formula to accomodate this varying data.

any help you could offer would be appreciated

best guess:


nlastrow = Range("A65536").End(xlUp).Row

=SUMPRODUCT(('Man balances'!AH2:A &lastrow)*('Man balances'!C2:C& lastrow=$A$115)*('Man balances'!AI2:AI & lastrow='Cash Balances'!I1))

thanks a lot

Jon

MaximS
06-09-2009, 12:52 AM
try that

Range("A1:A10").Formula = _
"=SUMPRODUCT(('Man balances'!AH$2:AH$" _
& nlastrow & ")*('Man balances'!C$2:C$" _
& nlastrow & "=$A$115)*('Man balances'!AI$2:AI$" _
& nlastrow & "='Cash Balances'!I1))"

Bob Phillips
06-09-2009, 12:59 AM
Why not just create dynamic name ranges and use those within the formula?

JZB
06-09-2009, 01:00 AM
showing an "application defined or object defined error" :think:

JZB
06-09-2009, 01:07 AM
i not sure i understand xld.

to give a bit more colour to what i doing. i have some raw data with account balances, account names and currencies.

on a summary sheet i need to sum the account balance per currency in row A across each account name (going across the columns).

so in the above formulae:

Man balances row AH isthe cash balance
Man balances row C is the CCY
Man balances row AI is the account name

A115 is the currecny to match to
I1 is the name to match to

MaximS
06-09-2009, 01:07 AM
check if all sheets in the code are exact same as ones in your workbook (case sensitive) and nlastrow need to be declared.

BTW. xld idea of dynamic named ranges might be a better option

Bob Phillips
06-09-2009, 01:10 AM
Sorry, scotch that.