Insert " " into a string

[Guest]

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

 

[Guest]

Try:-

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

Mike

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mark Lincoln]

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

 

[Ron Rosenfeld]

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