Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting question; function, formula, or coding? | Excel Worksheet Functions | |||
Coding a SumProduct formula | Excel Programming | |||
Help with Coding a Formula Please | Excel Programming | |||
Formula Color Coding | Excel Discussion (Misc queries) | |||
vba coding for formula in cell | Excel Programming |