Incorporating lastrow variables into sumproduct formula

[JZB]

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]

Are you wanting the macro to write the adjusted formula to your worksheet?

[JZB]

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]

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]

Why not just create dynamic name ranges and use those within the formula?

[JZB]

showing an "application defined or object defined error" :think:

[JZB]

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]

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]

Sorry, scotch that.