Help creating a custom function in VBA
 

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"),SUMPRO DUCT((range=cell
value)*(range)), IF(OR(cell="text",C5="text"),SUMPRODUCT((range=cel l
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

Help creating a custom function in VBA
 

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



" wrote:

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"),SUMPRO DUCT((range=cell
value)*(range)), IF(OR(cell="text",C5="text"),SUMPRODUCT((range=cel l
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

Help creating a custom function in VBA
 

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)



wrote in message
...
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"),SUMPRO DUCT((range=cell
value)*(range)), IF(OR(cell="text",C5="text"),SUMPRODUCT((range=cel l
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