I don't quite understand what you are doing. Not sure what values are names
ranges, which are integers but this code below shoiuld help you get started.

Function Revenue(ValueCenter As String, YTD_B As Single, YTD_C As Single, _
YTD_D As Single, Val1 As Variant, Val2 As Variant, Val3 As Variant, _
Actual As Single, All As Boolean)

If (YTD_C = Val(Range(ValueCenter))) And (YTD_C = Val(Val1)) And _
(YTD_D < Val(Val2)) And (YTD_D < Val(Val3)) Then

Revenue = -1 * Val(Actual)
End If
End Function


"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.