CODEWITHSUNDEEP
vbaexcel
Joseph Erickson
Joseph Erickson

Reputation: 960

Escaping Correctly VBA

I understand this question has been asked before but I found the answers difficult to incorporate and because of that I'm asking for my own specific problem:

Cells(counter, col).Value = "=MONTH(G2) & " - " & YEAR(G2)"

I tried:

Cells(counter, col).Value = "=MONTH(G2) & """ - """ & YEAR(G2)"

But that failed. I'm a little confused how to escape properly and after reviewing the stackoverflow responses to this question I was still confused. Thanks.

Upvotes: 0

Views: 72

Answers (3)

xidgel
xidgel

Reputation: 3145

I think what you're after is:

Cells(counter, col).Formula = "=MONTH(G2) & "" - "" & YEAR(G2)"

One way to get formulas with internal double quotes correct is to start by substituting a placeholder for the internal double quotes:

Cells(counter, col).Formula = "=MONTH(G2) & DBLQUOTE - DBLQUOTE & YEAR(G2)"

where the right hand side is a simple literal string. Then replace every occurrence of DBLQUOTE with "", the escaped version of a double quote.

Note that I changed .Value to .Formula --- I think that's better.

Hope that helps.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

Assuming you want the number-pair rather than a true date or a formula. Here is a way to use worksheet functions within a VBA sub:

Sub Joseph()
    counter = 2
    col = 2
    Cells(counter, col).Value = "'" & [MONTH(G2)] & " - " & [YEAR(G2)]
End Sub

enter image description here

The single quote prevents Excel from converting the number-pair into a Date.

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

You want to achieve this:

=MONTH(G2) & " - " & YEAR(G2)

So the string value for it needs to double-up the double quotes, like this:

=MONTH(G2) & "" - "" & YEAR(G2)

Therefore:

ActiveSheet.Cells(counter, col).Value = "=MONTH(G2) & "" - "" & YEAR(G2)"

Upvotes: 4

Related Questions