Coding Formula

[Richard]

I have a very complex formula as follows:

=IF(ISERR(FIND("COLLATERAL",BZ2))=FALSE,"Overige_Beleggingen",IF(AND(ISNA(VLOOKUP($H2,Vast_Table,2,FALSE))=FALSE,AB2<>"CASH"),VLOOKUP($H2,Vast_Table,2,FALSE),IF(OR(AB2="CASH",ISERR(FIND("COLLATERAL",BZ2))=FALSE,ISERR(FIND("ILF",BZ2))=FALSE),"Overige_Beleggingen","Aandelen")))

Which I would like to have entered into my spreadsheet using code. The
spreadsheet changes size ie no. of rows each time it is used so I want to
ensure that all necessary cell have the correct formula.

It would appear that due to it's complexity with quotes being used a number
of times within the nested functions, that I cannot simply use
Range("A2").Formula =

Could someone please advise on the best solution.

Many Thanks

Richard

 

[Don Guillett]

Try just using a double quote for each

 

[Mike H]

Hi,

To enter your formula into a cell using VBA try this:-

Sub servient()
Range("A1").Formula =
"=IF(ISERR(FIND(""COLLATERAL"",BZ2))=FALSE,""Overige_Beleggingen"",IF(AND(ISNA(VLOOKUP($H2,Vast_Table,2,FALSE))=FALSE,AB2<>""CASH""),VLOOKUP($H2,Vast_Table,2,FALSE),IF(OR(AB2=""CASH"",ISERR(FIND(""COLLATERAL"",BZ2))=FALSE,ISERR(FIND(""ILF"",BZ2))=FALSE),""Overige_Beleggingen"",""Aandelen"")))"
End Sub

You will need to provide more information on where and under what conditions
you want this formula filling other cells.

Mike