Try This

in any cell put =UDF and enter

Function UDF()

Set Val1 = Range("B504")
Set Val2 = Range("C504")
Set Val3 = Range("H504")
Set Val4 = Range("B504,D504,C504,E504,G504")

Ln = Len(Range("G504"))

For Each cell In Val4
Vresult = Vresult & cell
Next

Vresult1 = Left(Vresult, Len(Vresult) - Ln)

If Val1 = "NEW INSULATION" Then
UDF = Vresult
ElseIf Val1 = "NEW CLADDING" And Val2 = "PIPE" Then
UDF = Vresult
ElseIf Val1 = "NEW CLADDING" Then
UDF = Vresult1
ElseIf Val1 = "REMOVE CLADDING" Or Val1 = "REINSTALL CLADDING" Then
UDF = Range("B504") & Range("C504") & Range("E504")
ElseIf Val1 = "REMOVE INSULATION" Or Val1 = "REINSTALL INSULATION" Then
Select Case Val2
Case "PIPE", "FLAT", "ELBOW 900", "FLANGE", "VALVE"
Val2 = True
End Select

If Val3 = "COLD" And Val2 = True Then
UDF = Vresult1
ElseIf Val1 = "REMOVE INSULATION" Or Val1 = "REINSTALL INSULATION"
Then
UDF = Vresult1
End If

Else
UDF = 0
End If

End Function





"Aligahk06" wrote:

Dear All,

Can i convert the following formula in VBA UDF.

=IF($B504="NEW
INSULATION",CONCATENATE($B504,$D504,$C504,$E504,$G 504),IF(AND($B504="NEW
CLADDING",$C504="PIPE"),CONCATENATE($B504,$D504,$C 504,$E504,$G504),IF($B504="NEW
CLADDING",CONCATENATE($B504,$D504,$C504,$E504),IF( OR($B504="REMOVE
CLADDING",$B504="REINSTALL
CLADDING"),CONCATENATE($B504,$C504,$E504),IF(OR($B 504="REMOVE
INSULATION",$B504="REINSTALL
INSULATION"),IF(AND($H504="COLD",OR($C504="PIPE",$ C504="FLAT",$C504="ELBOW
900",$C504="FLANGE",$C504="VALVE")),CONCATENATE($B 504,$D504,$C504,$E504),IF(OR($B504="REMOVE
INSULATION",$B504="REINSTALL
INSULATION"),CONCATENATE($B504,$D504,$C504,$E504), 0)))))))


Please assist ?

Rgds,
Aligahk06