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

Try just using a double quote for each


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Richard" <(E-Mail Removed)> wrote in message
news:4C2378A6-96A0-4303-B5A6-(E-Mail Removed)...
>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

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

"Richard" wrote:

> 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