Excel VBA Coincatenate Cells containing - & :

Question

I'm modifying some code which adds links between a Summary Sheet and costing sheets. I'm trying to code the following formula to every CS sheet in cell C1. =CONCATENATE(SUMMARY!L9," - ",SUMMARY!M9," : ",SUMMARY!N9), where the row will change. I'm having trouble with the syntax. What is the easiest way to achieve this?

Code below (which almost works but does not put the required " into the formula

Sub CSrefs()
'
' Adds links from Summary Sheet to CS Sheets: - Updated 29/09/15 PRS

Dim i As Integer, iOffset As Integer, intCount As Integer
Dim intCS1_Index As Integer, intCSCount As Integer, nonCSSheets As Integer
Dim sFormulaText As String, sDash As String, sDots As String

Application.ScreenUpdating = False

    intCount = ActiveWorkbook.Sheets.Count      'Find total number of workbook sheets
    intCS1_Index = Sheets("CS1").Index          'CS1 Sheet index
    intCSCount = intCount - (intCS1_Index - 1)  'Find total number of CS sheets
    nonCSSheets = intCount - intCSCount         'Find total number of Non-CS sheets
    sDash = " - "
    sDots = " : "

For i = 1 To intCSCount 'number of sheets

    iOffset = i + 8    'Start of Summary Sheet CS Rows
        With Sheets("CS" & i)
            sFormulaText = "=CONCATENATE(SUMMARY!L" & iOffset & "," & sDash & ",SUMMARY!M" & iOffset & "," & sDots & ",SUMMARY!N" & iOffset & ")"
'            .Range("B1").Formula = sFormulaText
            .Range("A1").Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Summary!D" & iOffset, TextToDisplay:="SUMMARY" 'Go to Summary Sheet hyperlink
            .Range("A1").Font.Size = 8
            .Range("G1").Formula = "=SUMMARY!O" & iOffset ' UOM
            .Range("E1").Formula = "=SUMMARY!P" & iOffset 'SOR Qty
            .Range("I1").Formula = "=SUMMARY!R" & iOffset 'Cable Average
'            .Range("H10").Formula = "=SUMMARY!C" & iOffset 'Sage Code
            .Range("N17").Formula = "=SUMMARY!AC" & iOffset 'Materials GP%
            .Range("P17").Formula = "=SUMMARY!AP" & iOffset 'Materials MCD GP%
            .Range("N42").Formula = "=SUMMARY!AD" & iOffset 'Misc GP%
            .Range("P42").Formula = "=SUMMARY!AP" & iOffset 'Misc MCD GP%
            .Range("N67").Formula = "=SUMMARY!AE" & iOffset 'Labour GP
            .Range("P67").Formula = "=SUMMARY!AP" & iOffset 'Labour MCD GP%
            .Range("N92").Formula = "=SUMMARY!AF" & iOffset '3rd Party GP%
            .Range("P92").Formula = "=SUMMARY!AP" & iOffset '3rd Party MCD GP%
            .Range("N117").Formula = "=SUMMARY!AG" & iOffset 'PM&PL GP%
            .Range("P117").Formula = "=SUMMARY!AP" & iOffset 'PM&PL MCD GP%
            .Range("N142").Formula = "=SUMMARY!AH" & iOffset 'Bond GP%
            .Range("P142").Formula = "=SUMMARY!AP" & iOffset 'Bond MCD GP%
            .Range("N152").Formula = "=SUMMARY!AG" & iOffset 'Support GP%
            .Range("P152").Formula = "=SUMMARY!AP" & iOffset 'Support MCD GP%
            .Range("N177").Formula = "=SUMMARY!AH" & iOffset 'Day Works GP%
            .Range("P177").Formula = "=SUMMARY!AP" & iOffset 'Day Works MCD GP%
        End With

Next i

    Sheets("Summary").Select
    Application.ScreenUpdating = True

End Sub

Which line is the problematic one? the `sFormulaText` assignment? It's usually a good idea to minimize the amount of code you put up in your post. Side note, you should probably fetch the *list separator* character from the environment settings rather than hard-coding it - that hard-coded comma makes your code fail if the *list separator* is ever changed to anything other than a comma on the machine that runs this macro. — Mathieu Guindon, Feb 22 '17 at 15:55
Thanks @Mat's Mug. It's the the sFormulaText assignment. Where can I find your original answer? — Paul_S, Feb 22 '17 at 16:03
There's a link at the top of the question. Also JiheL's answer is correct, just not his code (see my comment under it). — Mathieu Guindon, Feb 22 '17 at 16:04
Thanks @Mats Mug. Copy & Pasting that gives' a Compile error - Expected end of statement at the first occurrence of ""-""", — Paul_S, Feb 22 '17 at 16:09
Thank you both. @Jihel. It works now. I new it was just the syntax. — Paul_S, Feb 22 '17 at 16:16

JiheL · Accepted Answer · 2017-02-22T16:09:05.173

1

Use twice double quotes inside double quotes to make one double quotes appear

sFormulaText = "=CONCATENATE(SUMMARY!L" & iOffset & ",""-"",SUMMARY!M" & iOffset & ", "":"",SUMMARY!N" & iOffset & ")"

Not sure if you need space between double quotes and dash / dots but you can add it yourself :)

edited Feb 22 '17 at 16:09

answered Feb 22 '17 at 15:55

JiheL

106
6

Thanks @Jihel. I had tried that but it comes up with a Type mismatch error – Paul_S Feb 22 '17 at 16:02
The quotes are mismatched. Should be `sFormulaText = "=CONCATENATE(SUMMARY!L" & iOffset & ","""-""","SUMMARY!M" & iOffset & ", """:""","SUMMARY!N" & iOffset & ")"` – Mathieu Guindon Feb 22 '17 at 16:04

Excel VBA Coincatenate Cells containing - & :

1 Answers1