CODEWITHSUNDEEP
excelvbaexcel-formula
user5219796
user5219796

Reputation:

Add formula with variables to active cell

I want to add a formula to the ActiveCell. The formula shall contain 2 variables one being a string the other one being a range.

i.e.

x = "This is my string variable"

y = cell.Address

I'm trying to achieve something like: 

ActiveCell.Formula = "=" & x & y

The cell should contain the string variable and the value of the variable containing the address. The behavior should be the same as if I would type ="Some string"&$a$1 into a cell.

Upvotes: 1

Views: 3342

Answers (2)

Jpad Solutions
Jpad Solutions

Reputation: 332

You need to put the variable x in quotes, and add the ampersand, like so:

ActiveCell.Formula = "=" & Chr(34) & x & Chr(34) & "&" & y

Another way is to double up on quotes:

ActiveCell.Formula = "=""" & x & """&" & y

This is a good answer to that part: How do I put double quotes in a string in vba?

UPDATED FOR BRACKETS AROUND Y, and some variables to make reading it easier:

    Sub Blah()
    Dim strText As String
    Dim strCellRef As String
    Dim strDoubleQuotes As String

    Dim strSpace As String

        strText = "This is my string variable"
        strCellRef = "$A$1"

        strDoubleQuotes = Chr(34)
        strSpace = " "

       ActiveCell.Formula = "=" & strDoubleQuotes & strText & strSpace & _
                        strDoubleQuotes & "&" & strDoubleQuotes & "(" & strDoubleQuotes & "&" & strCellRef & "&" & strDoubleQuotes & ")" & strDoubleQuotes

    End Sub

Upvotes: 3

SBF
SBF

Reputation: 1369

ActiveCell.FormulaR1C1 = "=""This is my string variable"" & RC[+1]"

where R[rowoffset]C[columnoffset] points to the cell relative to the active cell.
In my example RC[+1] is the cell right to the active cell (at the same row)

Upvotes: 0

Related Questions