Conversion of Nested if Formula in UDF.
 

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

Conversion of Nested if Formula in UDF.
 

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

Pass Values into the UDF
 

By creating arguments which can be passed into your UDF it does not need to work only for the one set of cells.

eg.

Public Function UDF(rng1 as range, rng2 as range, rng3 as range, rng4 as range) as variant
dim vResult as variant

'' do work with ranges here and change vResult to your desired outcome

UDF = vResult
end function



muddan madhu wrote:

Try Thisin any cell put =UDF and enterFunction UDF()Set Val1 =
27-Oct-09

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:

Previous Posts In This Thread:

On Tuesday, October 27, 2009 9:44 AM
Aligahk06 wrote:

Conversion of Nested if Formula in UDF.
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

On Tuesday, October 27, 2009 11:47 AM
muddan madhu wrote:

Try Thisin any cell put =UDF and enterFunction UDF()Set Val1 =
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:


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# And The Little Iterator That Could
http://www.eggheadcafe.com/tutorials...e-iterato.aspx