Cnverting Excel if into UDF.

[aligahk06]

Dear All,

Can i convert below nested if function into a UDF.

Please assist ?
Rgds,
Aligahk06

=IF($B696="NEW INSULATION",CONCATENATE($B696,$D696,$C696,$E696,$G696),IF(AND($B696="NEW CLADDING",$C696="PIPE"),CONCATENATE($B696,$D696,$C696,$E696,$G696),IF($B696 ="NEW CLADDING",CONCATENATE($B696,$D696,$C696,$E696),IF(OR($B696="REMOVE CLADDING",$B696="REINSTALL CLADDING"),CONCATENATE($B696,$C696,$E696),IF(OR($B696="REMOVE INSULATION",$B696="REINSTALL INSULATION"),IF(AND($H696="COLD",OR($C696="PIPE",$C696="FLAT",$C696="ELBOW 900",$C696="FLANGE",$C696="VALVE")),CONCATENATE($B696,$D696,$C696,$E696),IF (OR($B696="REMOVE INSULATION",$B696="REINSTALL INSULATION"),CONCATENATE($B696,$D696,$C696,$E696),0)))))))

[Bob Phillips]

A stab

[vba]

Function myFunc(cell1 As Range, cell2 As Range, cell3 As Range, cell4 As Range, cell5 As Range, cell6 As Range) As Variant

If cell1 = "NEW INSULATION" Then

myFunc = cell1 & cell3 & cell2 & cell4 & cel5
ElseIf cell1 = "NEW CLADDING" And cell2 = "PIPE" Then

myFunc = cell1 & cell3 & cell2 & cell4 & cel5
ElseIf cell1 = "NEW CLADDING" Then

myFunc = cell1 & cell3 & cell2 & cell4
ElseIf cell1 = "REMOVE CLADDING" Or cell1 = "REINSTALL CLADDING" Then

myFunc = cell1 & cell2 & cell4
ElseIf cell1 = "REMOVE INSULATION" Or cell1 = "REINSTALL INSULATION" Then

If cell6 = "COLD" And (cell2 = "PIPE" Or cell2 = "FLAT" Or cell2 = "ELBOW 900" Or cell2 = "FLANGE" Or cell2 = "VALVE") Then

myFunc = cell1 & cell3 & cell2 & cell4
ElseIf cell1 = "REMOVE INSULATION" Or cell1 = "REINSTALL INSULATION" Then

myFunc = cell1 & cell3 & cell2 & cell4
Else

myFunc = 0
End If
End If
End Function
[/vba]

=myFunc(b696,C696,D696,e696,G696,h696)

[Zack Barresse]

Hello there,

I think the question here should be why do you want to make this a UDF? Obviously it works the way it is, and you're not going to gain any performance out of things. In fact I'm willing to bet you're going to see a performance drop by using this as a UDF. So whilst the answer to your question is yes, (and maybe it's wrong of me) I re-submit a question: why?

[Bob Phillips]

Hello there,

I think the question here should be why do you want to make this a UDF? Obviously it works the way it is, and you're not going to gain any performance out of things. In fact I'm willing to bet you're going to see a performance drop by using this as a UDF. So whilst the answer to your question is yes, (and maybe it's wrong of me) I re-submit a question: why?

I agree, but as I get older but no wiser I tend to stop asking such question s

[aligahk06]

I agree, but as I get older but no wiser I tend to stop asking such question s

I agree too ? But sometime knowing the expert ideas about new things is good to experiment.