I am inserting formulas into cells using a macro. Several formulas require a
blank. (" ")

Ex1: Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," &
CSpath & ",29,FALSE))," ",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Ex2: Sheets("Frame 6").Range("F3").Value = "=IF(D3=1,VLOOKUP(A3," & CSpath &
",34,FALSE)," ")"

However, Excel won't let you do this. How can I get the blanks in there?
Thanks in advance.

-Chris

Try:-

Double double quotes for the spaces i.e. "" ""

Mike

"CWillis" wrote:

I am inserting formulas into cells using a macro. Several formulas require a
blank. (" ")

Ex1: Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," &
CSpath & ",29,FALSE))," ",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Ex2: Sheets("Frame 6").Range("F3").Value = "=IF(D3=1,VLOOKUP(A3," & CSpath &
",34,FALSE)," ")"

However, Excel won't let you do this. How can I get the blanks in there?
Thanks in advance.

-Chris

I already tried that. Worked this time though. Thanks.

"Mike H" wrote:

Try:-

Double double quotes for the spaces i.e. "" ""

Mike

"CWillis" wrote:

I am inserting formulas into cells using a macro. Several formulas require a
blank. (" ")

Ex1: Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," &
CSpath & ",29,FALSE))," ",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Ex2: Sheets("Frame 6").Range("F3").Value = "=IF(D3=1,VLOOKUP(A3," & CSpath &
",34,FALSE)," ")"

However, Excel won't let you do this. How can I get the blanks in there?
Thanks in advance.

-Chris

I am inserting formulas into cells using a macro. Several formulas require
a
blank. (" ")

Ex1: Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," &
CSpath & ",29,FALSE))," ",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Ex2: Sheets("Frame 6").Range("F3").Value = "=IF(D3=1,VLOOKUP(A3," & CSpath
&
",34,FALSE)," ")"

However, Excel won't let you do this. How can I get the blanks in there?
Thanks in advance.

Because VBA uses quote marks to delineate String constants, you can't just
use them as is inside of a String constant in order to include them within
that String constant. If you double them up, however, VBA will treat the
doubled up quote marks as if they were a single included quote mark. Here is
your first example modified to account for this...

Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," & CSpath &
",29,FALSE)),"" "",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Notice that your " " has become "" "".

Rick

You can also use Chr() to insert spaces:

Sub MakeSpace
Range("A1").Value = "Make" & Chr(32) & "space"
Range("A2").Value = "Make" & Chr(vbKeySpace) & "space"
End Sub

vbKeySpace is the VBA constant for the Space key.

Mark Lincoln

On Jun 12, 9:16 am, CWillis wrote:
I am inserting formulas into cells using a macro. Several formulas require a
blank. (" ")

Ex1: Sheets("Frame 6").Range("G3").Value = "=IF(ISERROR(VLOOKUP(A3," &
CSpath & ",29,FALSE))," ",VLOOKUP(A3," & CSpath & ",29,FALSE))"

Ex2: Sheets("Frame 6").Range("F3").Value = "=IF(D3=1,VLOOKUP(A3," & CSpath &
",34,FALSE)," ")"

However, Excel won't let you do this. How can I get the blanks in there?
Thanks in advance.

-Chris

On Tue, 12 Jun 2007 06:16:00 -0700, CWillis
wrote:

I am inserting formulas into cells using a macro. Several formulas require a
blank. (" ")


Others have posted the solution. However, a word of caution. With your " "
you are NOT inserting a "blank", rather you are inserting a <space character.

If you want to insert a null string, which is generally what is meant when
someone writes about inserting a blank, you should not include the <space.

So instead of "" "" you should be using """"
--ron