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!

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!

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!

Insert Formula
 

double quotes

Sub placeformula()
Range("j1").Formula = "=if(b5="""","""",1)"
End Sub

so yours
Worksheets(1).Range("C2").Formula = "=if(B2="""" ,"""",VLOOKUP
(B2,Z2:AA9,2))"

--
Don Guillett
SalesAid Software

"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!

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!
.