Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
In one field I have an change module that, amound other
things sets the value of a field in my spreadsheet if certain conditions exist to a vlookup formula. The line of code is as follows: Worksheets(1).Range("C2").Formula = "=if(B2="" , "",VLOOKUP (B2,Z2:AA9,2))" What ends up in the cell is the following: =IF(B2=" , ",VLOOKUP(B2,Z2:AA9,2)) which evaluates to false instead of doing what I need and retrieving the desired value. The insert worked correctly until I addded the if statement. I have validation built in to prevent a null or empty value in the lookup cell but I needed to handdle it initially in case the user had not actually chosen a value. Any help would be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
Hi Kevin,
If u write a formula in the code then if u have a single quote in the formula then put double quotes in the code, if u have double quotes then put 4 and so on. So your formula shud read as Worksheets(1).Range("C2").Formula = "=if(B2="""" , """",VLOOKUP(B2,Z2:AA9,2))" Regards, Hari India "Kevin" wrote in message ... In one field I have an change module that, amound other things sets the value of a field in my spreadsheet if certain conditions exist to a vlookup formula. The line of code is as follows: Worksheets(1).Range("C2").Formula = "=if(B2="" , "",VLOOKUP (B2,Z2:AA9,2))" What ends up in the cell is the following: =IF(B2=" , ",VLOOKUP(B2,Z2:AA9,2)) which evaluates to false instead of doing what I need and retrieving the desired value. The insert worked correctly until I addded the if statement. I have validation built in to prevent a null or empty value in the lookup cell but I needed to handdle it initially in case the user had not actually chosen a value. Any help would be greatly appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
Hi
try: Worksheets(1).Range("C2").Formula = "=if(B2="""","""",VLOOKUP (B2,Z2:AA9,2))" -- Regards Frank Kabel Frankfurt, Germany Kevin wrote: In one field I have an change module that, amound other things sets the value of a field in my spreadsheet if certain conditions exist to a vlookup formula. The line of code is as follows: Worksheets(1).Range("C2").Formula = "=if(B2="" , "",VLOOKUP (B2,Z2:AA9,2))" What ends up in the cell is the following: =IF(B2=" , ",VLOOKUP(B2,Z2:AA9,2)) which evaluates to false instead of doing what I need and retrieving the desired value. The insert worked correctly until I addded the if statement. I have validation built in to prevent a null or empty value in the lookup cell but I needed to handdle it initially in case the user had not actually chosen a value. Any help would be greatly appreciated! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Formula
Thankyou one and all!
-----Original Message----- In one field I have an change module that, amound other things sets the value of a field in my spreadsheet if certain conditions exist to a vlookup formula. The line of code is as follows: Worksheets(1).Range("C2").Formula = "=if (B2="" , "",VLOOKUP (B2,Z2:AA9,2))" What ends up in the cell is the following: =IF(B2=" , ",VLOOKUP(B2,Z2:AA9,2)) which evaluates to false instead of doing what I need and retrieving the desired value. The insert worked correctly until I addded the if statement. I have validation built in to prevent a null or empty value in the lookup cell but I needed to handdle it initially in case the user had not actually chosen a value. Any help would be greatly appreciated! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Row with formula | Excel Discussion (Misc queries) | |||
insert formula | Excel Worksheet Functions | |||
How to insert a formula..but... | Excel Discussion (Misc queries) | |||
How to insert = in formula bar | Excel Discussion (Misc queries) | |||
insert a new row by formula | Excel Worksheet Functions |