Coding Formula
 

I have a very complex formula as follows:

=IF(ISERR(FIND("COLLATERAL",BZ2))=FALSE,"Overige_B eleggingen",IF(AND(ISNA(VLOOKUP($H2,Vast_Table,2,F ALSE))=FALSE,AB2<"CASH"),VLOOKUP($H2,Vast_Table,2 ,FALSE),IF(OR(AB2="CASH",ISERR(FIND("COLLATERAL",B Z2))=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

Coding Formula
 

Try just using a double quote for each


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Richard" wrote in message
...
I have a very complex formula as follows:

=IF(ISERR(FIND("COLLATERAL",BZ2))=FALSE,"Overige_B eleggingen",IF(AND(ISNA(VLOOKUP($H2,Vast_Table,2,F ALSE))=FALSE,AB2<"CASH"),VLOOKUP($H2,Vast_Table,2 ,FALSE),IF(OR(AB2="CASH",ISERR(FIND("COLLATERAL",B Z2))=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

Coding Formula
 

Hi,

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

Sub servient()
Range("A1").Formula =
"=IF(ISERR(FIND(""COLLATERAL"",BZ2))=FALSE,""Overi ge_Beleggingen"",IF(AND(ISNA(VLOOKUP($H2,Vast_Tabl e,2,FALSE))=FALSE,AB2<""CASH""),VLOOKUP($H2,Vast_ Table,2,FALSE),IF(OR(AB2=""CASH"",ISERR(FIND(""COL LATERAL"",BZ2))=FALSE,ISERR(FIND(""ILF"",BZ2))=FAL SE),""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_B eleggingen",IF(AND(ISNA(VLOOKUP($H2,Vast_Table,2,F ALSE))=FALSE,AB2<"CASH"),VLOOKUP($H2,Vast_Table,2 ,FALSE),IF(OR(AB2="CASH",ISERR(FIND("COLLATERAL",B Z2))=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