Quote Marks Through VBA

B

brett.kaplan

Very simple question, but I can't figure it out:

I want to make a formula in a cell through VBA, but I want the formula
to have a word in it (which needs to be in quotes). That is, I want
the formula to read:

=IF(Sheet2!G6="Maturity",1,2)

How can I get the Maturity part surrounded by quotes?

Thanks!

Brett
 
D

Don Guillett

You did it right if the ref cell does have maturity in it. Maybe leading or
trailing space?
=IF(TRIM(F1)="maturity",1,2)
 
G

Gary''s Student

I put them in explicitly thru chr():


Sub brett()
Dim s As String
s = "=IF(Sheet2!G6=" & Chr(34) & "Maturity" & Chr(34) & ",1,2)"
Cells(1, 1).Formula = s
End Sub
 
B

brett.kaplan

Thanks! That works - is there a place I can see all the character code
numbers?
 
G

Gary''s Student

Fill column A with all the keyboard characters:

A
B
C
..
..
..

both upper and lower case and the keys like > as well


Then in B1 enter:
=CODE(A1) and copy down.
 
G

Gizmo63

By far the simplest way is to double up the quotes, so the code looks like
this:

Range([range to insert formula]) = "=IF(Sheet2!G6=""Maturity"",1,2)"

HTH

Giz
 
B

Bob Phillips

=IF(Sheet2!G6=""Maturity"",1,2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dana DeLouis

Just an idea. Some like to use a custom function for the quotes.
A function named "Q" seems to be popular. (another might QQ for Double
quotes)

[A1] = "=IF(Sheet2!G6=" & Q("Maturity") & ",1,2)"

Sometimes this can help if the equation is complicated.
 
D

dolivastro

Doubling quotes is not sufficient. You need to triple them. Or am I
missing something?

=IF(Sheet2!G6="""Maturity""",1,2)

The way I see it is this. The 1st quote just begins the quote
sequence. The second quote would normally end it, but since it is
followed by a quote, it is interpreted as a "hard" quote instead. Then
the fourth quote would normally end it, but it too is followed by a
quote so it is made hard, and the then 6th quote just ends the whole
shebang.

Dom
 
K

kassie

You are right about the triple part, but not about the placement. The first
set of quotes include the entire formula "=IF(Sheet2!G6=""Maturity"",1,2"
 
B

Bob Phillips

I responded in the style of the question, therefore triple is not needed, it
will either be placed into a variable, in which case it will need enclosing
within quotation marks, or it will be a property value in-line, in which
case it would also be enclosed within quotation marks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Gord Dibben

In A1 enter =CHAR(ROW())

Copy down to row 256 to see the associated characters.


Gord Dibben MS Excel MVP
 
Top