Conversion of Nested if Formula in UDF.

  • Thread starter Thread starter Aligahk06
  • Start date Start date
A

Aligahk06

Dear All,

Can i convert the following formula in VBA UDF.

=IF($B504="NEW
INSULATION",CONCATENATE($B504,$D504,$C504,$E504,$G504),IF(AND($B504="NEW
CLADDING",$C504="PIPE"),CONCATENATE($B504,$D504,$C504,$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($B504="REMOVE
INSULATION",$B504="REINSTALL
INSULATION"),IF(AND($H504="COLD",OR($C504="PIPE",$C504="FLAT",$C504="ELBOW
900",$C504="FLANGE",$C504="VALVE")),CONCATENATE($B504,$D504,$C504,$E504),IF(OR($B504="REMOVE
INSULATION",$B504="REINSTALL
INSULATION"),CONCATENATE($B504,$D504,$C504,$E504),0)))))))


Please assist ?

Rgds,
Aligahk06
 
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
 
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 Thi

in any cell put =UDF and ente

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 Val
Vresult = Vresult & cel
Nex

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

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

If Val3 = "COLD" And Val2 = True The
UDF = Vresult
ElseIf Val1 = "REMOVE INSULATION" Or Val1 = "REINSTALL INSULATION
The
UDF = Vresult
End I

Els
UDF =
End I

End Functio



:

Previous Posts In This Thread:

Conversion of Nested if Formula in UDF.
Dear All

Can i convert the following formula in VBA UDF

=IF($B504="NE
INSULATION",CONCATENATE($B504,$D504,$C504,$E504,$G504),IF(AND($B504="NE
CLADDING",$C504="PIPE"),CONCATENATE($B504,$D504,$C504,$E504,$G504),IF($B504="NE
CLADDING",CONCATENATE($B504,$D504,$C504,$E504),IF(OR($B504="REMOV
CLADDING",$B504="REINSTAL
CLADDING"),CONCATENATE($B504,$C504,$E504),IF(OR($B504="REMOV
INSULATION",$B504="REINSTAL
INSULATION"),IF(AND($H504="COLD",OR($C504="PIPE",$C504="FLAT",$C504="ELBO
900",$C504="FLANGE",$C504="VALVE")),CONCATENATE($B504,$D504,$C504,$E504),IF(OR($B504="REMOV
INSULATION",$B504="REINSTAL
INSULATION"),CONCATENATE($B504,$D504,$C504,$E504),0))))))

Please assist

Rgds
Aligahk06

Try Thisin any cell put =UDF and enterFunction UDF()Set Val1 =
Try Thi

in any cell put =UDF and ente

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 Val
Vresult = Vresult & cel
Nex

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

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

If Val3 = "COLD" And Val2 = True The
UDF = Vresult
ElseIf Val1 = "REMOVE INSULATION" Or Val1 = "REINSTALL INSULATION
The
UDF = Vresult
End I

Els
UDF =
End I

End Functio



:


Submitted via EggHeadCafe - Software Developer Portal of Choice
C# And The Little Iterator That Could
http://www.eggheadcafe.com/tutorial...32-0ae26adaa533/c-and-the-little-iterato.aspx
 
Back
Top