Add formula with variables to active cell

Question

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.

Jpad Solutions · Accepted Answer · 2018-01-04T13:34:28.887

3

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

edited Jan 04 '18 at 13:34

answered Jan 04 '18 at 11:20

Jpad Solutions

332
1
12

np, I always use chr(34) just because I find it easier to read – Jpad Solutions Jan 04 '18 at 11:37
Jpad Limited, is there some sort of pattern or guideline how to add the double quotes ? I tried to add another string but didn't succeed – Jan 04 '18 at 12:52
not sure what you mean, any string variable will need quotes around it.. example? – Jpad Solutions Jan 04 '18 at 12:59
ActiveCell.Formula = "=""" & x & "" & "" & ( & "" "&" & y Want to add () around y – Jan 04 '18 at 13:03
1

It's an ugly business: ActiveCell.Formula = "=" & Chr(34) & x & " " & Chr(34) & "&" & Chr(34) & "(" & Chr(34) & "&" & y & "&" & Chr(34) & ")" & Chr(34) I have added some more code to my original answer – Jpad Solutions Jan 04 '18 at 13:30
wow, thanks a lot for your time and solution :) .. I think I'll write a small script to auto add double quotes, maybe others find it also confusing . – Jan 04 '18 at 13:39
it's very easy to write a function to auto add them, but it is more code to call it each time than to just add them..... The real issue here is knowing when you need to use them, and also the ampersand. Which I think generally comes from experience. – Jpad Solutions Jan 04 '18 at 13:41
I see, thanks for the info. Still I think it would be nice if you had a small app where you could choose what you want to do (add a formula, values, etc), add the values you want in the cell, press a button and get the correct format back. You think that wouldn't be easily achievable ? – Jan 04 '18 at 17:43

score 0 · Answer 2 · answered Jan 04 '18 at 11:28

0

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)

answered Jan 04 '18 at 11:28

SBF

1,252
3
12
21

Add formula with variables to active cell

2 Answers2