1

I'm trying to create a formula in the A1 cell which contains an ampersand character that looks something like this in Excel:

="Hello, "&A2

My VBA code is as follows:

Worksheets("Sheet1").Range("A1").Formula = "=""Hello, "" & "&A2"

VBA is throwing errors, and I can't figure out why. I've tried using double ampersands, and double quotes in various places and can't get around this error.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
ch-pub
  • 1,664
  • 6
  • 29
  • 52
  • It looks like that rather than the ampersand, you just have the extra double quote. See [this answer](https://stackoverflow.com/questions/33291221/inserting-a-formula-containing-an-ampersand-into-a-cell#comments-33291258) // See also: [excel - How do I put double quotes in a string in vba? - Stack Overflow](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba) – user202729 Aug 17 '21 at 13:18

5 Answers5

2

Consider:

Sub dural()
   Range("A1").Formula = "=" & Chr(34) & "Hello" & Chr(34) & "& A2"
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
1

Please consider this pithy line:

[a1] = "=""Hello, "" & A2"

...which will evaluate to, "Hello, Dave"

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Say he was working in Sheet2 - using `[a1]` would refer to Sheet2's A1, correct? So to be sure, would `Sheets("Sheet1").[a1]` work? Or do the brackets not work that way? – BruceWayne Oct 22 '15 at 23:20
  • 1
    If no sheet object is specified, then the result will be the active sheet. You can do what you wrote in your comment. And, you can also do: `[sheet1!a1]` – Excel Hero Oct 22 '15 at 23:22
0

You have one extra ":

Worksheets("Sheet1").Range("A1").Formula = "=""Hello, "" & A2"
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

Use the & character code of 38:

Worksheets("Sheet1").Range("A1").Formula = "=" & Chr(34) & "Hello, " & Chr(34) & Chr(38) & "A2"
Chrismas007
  • 6,085
  • 4
  • 24
  • 47
0

This should work as well.

 ActiveCell.FormulaR1C1 = "=""hello "" & A2"
Davesexcel
  • 6,896
  • 2
  • 27
  • 42
  • I want the formula in cell A1 to be `="Hello, "&A2 `. So if the value of A2 is "Dave", then the formula in cell A1 should evaluate to "Hello, Dave" – ch-pub Oct 22 '15 at 21:55
  • I think you were confusing everybody because of the "ampersand " in the title, edited code – Davesexcel Oct 22 '15 at 22:05
  • Thanks. That's why I provided the formula I'm attempting to insert. – ch-pub Oct 22 '15 at 22:09
  • 2
    When you have a working formula, record a macro, go into the formula bar and hit enter, then stop recording, go to the VBA editor to see what the formula looks like. – Davesexcel Oct 22 '15 at 22:12