Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

Quotes within a VBA CONCATENATE string formula to be put in worksheet

I've got the code below, which works fine to produce the following value:
(A1)=SAPMAC613P
(it figures out how many rows of data, then picks up the column A value, adds some text and the row number)

I want to have the formula produce the following result including the quotation marks:
A(1)="SAPMAC613P"
but I can't figure out how to get the other quotes to be put in.  I've tried the """" and Char(34) method but can't get it right.
Thanks.

Const ResultColumn = "E" 'column for result pasting
 
Dim lRow As Long
   lRow = Range("A" & Rows.Count).End(xlUp).Row
 
Range(Cells(1, ResultColumn), Cells(lRow, ResultColumn)).FormulaR1C1 = _
    "=CONCATENATE(""(A"" & row() & "")="" &  RC[-4] )"

Open in new window

0
SheaJeff
Asked:
SheaJeff
  • 3
1 Solution
 
wdosanjosCommented:
Try this:

Range(Cells(1, ResultColumn), Cells(lRow, ResultColumn)).FormulaR1C1 = _
    "=CONCATENATE(""A(" & row() & ")=""""" & RC[-4] & """"""")"

Open in new window

0
 
SheaJeffAuthor Commented:
I'm getting a Syntax Error with this one  :(
0
 
SheaJeffAuthor Commented:
I got it!
You were missing a couple of quotes.
Range(Cells(1, ResultColumn), Cells(lRow, ResultColumn)).FormulaR1C1 = _
    "=CONCATENATE(""A("" & row() & "")="""""" & RC[-4] & """""""")"

Open in new window

0
 
SheaJeffAuthor Commented:
Solution was pretty close; just missing some quotation marks; see my working code below
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now