Help creating a custom function in VBA

[daveb2]

I think I'm a bit out of my depth with by knowledge of VBA and I've
been trying to no avail to come up with some code to do encompass the
following array formula:

=IF(OR(cell="text",cell="text",cell="text"),SUMPRODUCT((range=cell
value)*(range)), IF(OR(cell="text",C5="text"),SUMPRODUCT((range=cell
value)*(range)),"wrong"))

My actual formula used in the sheet is:

=IF(OR(C5="insulation",C5="civil",C5="scaffolding",C5="scaffolding/
painting",C5="painting"),SUMPRODUCT((Contractor 1!$A$2:$A
$242='Measures PO'!$B5)*(Contractor 1!$H$2:$H$242)),
IF(OR(C5="mechanical - structural",C5="mechanical -
pipework",C5="electrical"),SUMPRODUCT((Contractor 2!$A$2:$A
$190='Measures PO'!$B5)*(Contractor 2!$H$2:$H$190)),"wrong"))

What I'd like to do is speed this up with a custom function that so I
can quickly obtain total costs and add more contactors (and in the
case of the text matches disciplines of work) as needed which will be
extremely limited by my array formula. I'm not even sure how possible
it is or if there'd be any other info someone would need me to provide
so they can help me, so any input is welcome.

Thanks

Dave

 

[Joel]

try something like this. I hard-coded the ranges into the function. they
can be passed as parameters.


Function totalCost(TypeofWork)
Select Case TypeofWork

Case "insulation", "civil", "scaffolding", "scaffolding/painting",
"painting"
ContractorNumber = 1
Case "mechanical - structural", "mechanical - pipework", "electrical"
ContractorNumber = 2
Case Else
totalCost = "wrong"
Exit Function
End Select

MeasureValue = Sheets("Measure PO").Range("B5")
total = 0
For Each cell In Sheets("Contractor " & ContractorNumber).Range("A2:A242")
If MeasureValue = cell.Value Then
total = total + cell.Offset(0, 7) 'add column h
End If
Next cell
End Function

 

[Bob Phillips]

You could improve what you have

=IF(OR(C5="insulation",C5="civil",C5="scaffolding",C5="scaffolding/painting",C5="painting"),SUMIF('Contractor
1'!$A:$A,'Measures PO'!$B5,'Contractor 1'!$H:$H),
IF(OR(C5="mechanical - structural",C5="mechanical -
pipework",C5="electrical"),SUMIF('Contractor 2'!$A:$A,'Measures
PO'!$B5,'Contractor 2'!$H:$H),"wrong"))

but here is a udf as well

Public Function CustomFunc(ByVal rng As Range, ByVal rng2 As Range)

If rng = "insulation" Or rng = "civil" Or rng = "scaffolding" Or rng =
"scaffolding/painting" Or rng = "painting" Then

CustomFunc = Application.SumIf(Worksheets("Contractor
1").Range("$A$2:$A$242"), rng2, Worksheets("Contractor
1").Range("$H$2:$H$242"))
ElseIf rng = "mechanical - structural" Or rng = "mechanical - pipework"
Or rng = "electrical" Then

CustomFunc = Application.SumIf(Worksheets("Contractor
2").Range("$A$2:$A$190"), rng2, Worksheets("Contractor
2").Range("$H$2:$H$190"))
Else

CustomFunc = "wrong"
End If
End Function




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)