First, I'm not sure why you use =value() around your terms. It may make sense
for cells that may not be numeric, but 191 and 999 are already numbers, so it's
not necessary/useful there.

Second, you have a couple of choices to convert the array formula into VBA.

#1. Loop through each of the cells and accumulate the total.
#2. Use Evaluate.

dim mySum as double
mysum = worksheets("Sheet999").evaluate("SUM((ytd.ccb=VALU E(BCostCentre))" _
& "*(ytd.ccc=VALUE($C19))" _
& "*(ytd.ccd<191)" _
& "*(ytd.ccd<999)" _
& "*(ytd.act))*-1)")



Forgone wrote:

I've got an Array Formula that I want to be able to convert to a VBA
function so that I can manipulate it without having to copy and paste
the formula a large number of times through out the 800 line workbook.

The base Formula, which uses Name ranges is:

Revenue:
=SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($ C19))*(ytd.ccd<VALUE(191))*(ytd.ccd<VALUE(999))* (ytd.act))*-1)

Expense:
=SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE($ C19))*(ytd.ccd<VALUE(191))*(ytd.ccd<VALUE(999))* (ytd.act)))

What I want to do is along the lines of....... if the values in
ytd.ccb, ytd.ccc and ytd.ccd are null then sum up everything otherwise
if there is a value, only do that value.

This is one of the formula modifications I've done.
=IF(BCostCentre="",SUM((ytd.ccc=VALUE($C19))*(ytd. ccd<VALUE(191))*(ytd.ccd<VALUE(999))*(ytd.act))*-1,SUM((ytd.ccb=VALUE(BCostCentre))*(ytd.ccc=VALUE( $C19))*(ytd.ccd<VALUE(191))*(ytd.ccd<VALUE(999)) *(ytd.act))*-1)

but I think it would be a lot easier to do it using a VBA function.

Ideally, I would like to use a wildcard string in those values, eg:
"***" which will tell the formula to sum all and not filter it based
on the results.

Any assistance would be appreciated.

Thanks.

--

Dave Peterson