0

I ask the user input for a string and then need to incorporate this into a formula. I searched on other questions but didn’t get the desired output.

Sname = InputBox("Enter name")

Cells(2, 32).FormulaR1C1 = _
=CONCATENATE(J2,""-"",K2,""-"",L2,""-"" "" & Sname & "" -"",T2,U2,V2,W2,X2,Y2,""-"",AB2,""-"",AC2)

suppose i enter AAA
i want the formula on cell(2,32) to be 

=CONCATENATE(J2,"-",K2,"-",L2,"-" & "AAA" & "-",T2,U2,V2,W2,X2,Y2,"-",AB2,"-",AC2)
Shashwat Aryal
  • 126
  • 1
  • 11
  • Possible duplicate of [Quotation Marks VBA](http://stackoverflow.com/questions/24454575/quotation-marks-vba) – vacip Mar 26 '16 at 10:18
  • @vacip I do have the double quotation mark? or is it that I'm not getting something? single quotation marks appear in the cell after i run the macro as well ` =CONCATENATE('J2', " - ", 'K2', " - ", 'L2', " - ", "&Sname&", " - ", 'T2', 'U2', 'V2', 'W2', 'X2', 'Y2', " - ", 'AB2', " - ", 'AC2') ` is what the cell displays after i run the module. the name of the variable is being displayed as well "Sname" it wanted it to display what i entered – Shashwat Aryal Mar 26 '16 at 10:29
  • @vacip that did slove part of the froblem but as i said in my comment on sythr's reply i cant get the Sname variable value into the formula – Shashwat Aryal Mar 26 '16 at 10:42
  • Ah, ok. Wrong flag then... It is not duplicate, but a simple typographical error. :) – vacip Mar 26 '16 at 10:45

2 Answers2

2

Try this:

Cells(2,32).Formula = _
 "=CONCATENATE(J2,""-"",K2,""-"",L2,""-"" & """ & Sname & """ & ""-"",T2,U2,V2,W2,X2,Y2,""-"",AB2,""-"",AC2)"

There was a slight mixup with your quotation marks around the variable.

This one works for me.

vacip
  • 5,246
  • 2
  • 26
  • 54
  • `=CONCATENATE(J2,"-",K2,"-",L2,"-" & " Sname " & "-",T2,U2,V2,W2,X2,Y2,"-",AB2,"-",AC2)` this is what appears on the cell as the formula. `Sname = InputBox("Enter name")` would be specified by the user and if suppose they enter AAA i want the forumla in the cell to be `=CONCATENATE(J2,"-",K2,"-",L2,"-" & "AAA " & "-",T2,U2,V2,W2,X2,Y2,"-",AB2,"-",AC2)` – Shashwat Aryal Mar 26 '16 at 10:48
  • My bad. Edited. Quotation marks can get you mixed up. :) – vacip Mar 26 '16 at 10:50
  • thankyou so much ^_^ ive just been learning VB for a few weeks no wonder if its confusing to someone as good as you i had a hard time. – Shashwat Aryal Mar 26 '16 at 10:55
0

swap " for ' Tip: you can use CTRL+U

=CONCATENATE(J2,'-',K2,'-',L2,'-' & 'AAA' & '-',T2,U2,V2,W2,X2,Y2,'-',AB2,'-',AC2)

Add a replace function

Replace("=CONCATENATE(J2,'-',K2,'-',L2,'-' & 'AAA' & '-',T2,U2,V2,W2,X2,Y2,'-',AB2,'-',AC2)","'", Chr(34))

Chr(34) equal to caracter "

Laionel Lellis
  • 1