CODEWITHSUNDEEP
excelvbaformula
Mel
Mel

Reputation: 21

Formula is giving error when I add "text"

I'm trying to write a Macro that will do the formatting of a report. All is well up until I try to create the transaction reference number.

The formula basically goes =A2 & "-" & B2 & "ABCD"

When I insert this into my macro it becomes

Range("C2").Formula = "=A2 & "-" & B2 & "ABCD""

As soon as I add my hyphen or the letters at the end it gives me "Compile Error: Expected: end of statement"

What am I doing wrong?

Edit: So the output would be contentOfA2-contentOfB2 ABCD

Upvotes: 1

Views: 158

Answers (2)

Vityata
Vityata

Reputation: 43585

Try to do it reversed - e.g. write the formula in excel like this: enter image description here

Then select the formula and write the following in the VBA:

Public Sub TestMe()

    Debug.Print Selection.Formula

End Sub

On the direct window you would have this: =A2&"-"&B2&"abcd" This input should be edited a bit, because it comes from the console and you need to escape the ". Like this: "=A2 & ""-"" & B2 & ""abcd"""

You can elaborate further, by writing Debug.Print Selection.FormulaR1C1, if you need it.

At the end, you can use something like this, to escape the counting of the parenthesis. It would work ok:

Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.Formula
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub

It changes the single quote to a double and then it puts the formula in a quote. In my code, it is code strParenth.

Upvotes: 0

Jared
Jared

Reputation: 143

Try this. When adding quotes inside a string, double up the quotes so the program knows you aren't simply ending the string.

Range("c2").Formula = "=A2 & ""-"" & B2 & ""ABCD"""

Example..

Range("A1").value = "adding a quote inside a string ""so and so said something"" is like this."

output: adding a quote inside a string "so and so said something" is like this.

Upvotes: 2

Related Questions