CODEWITHSUNDEEP
vbaexcel
Student of the Digital World
Student of the Digital World

Reputation: 3041

Concat in Excel using VBA and output it to a string

I have a requirement where I am trying to concatenate the values from Cell A1 to M1. The sheets are dynamic.

I am trying to concatenate the values from cell A1 to M1 to a string using VBA.

How to do this ? The sheets are dynamic, so when we select the range I want the sheets to be mentioned in the code as well.

This is how excel generates a macro, if I do the concat on cell N1,

ActiveCell.FormulaR1C1 = "=CONCAT(RC[-13]:RC[-1])"

Thanks. Kindly share your thoughts.

Upvotes: 0

Views: 5108

Answers (2)

Mrig
Mrig

Reputation: 11702

Something like this

Sub Demo()
    Dim ws As Worksheet
    Dim cel As Range
    Dim str As String
    Set ws = ThisWorkbook.Sheets("Sheet4")   'change Sheet4 to your data sheet
    For Each cel In Range(ws.Range("A1"), ws.Range("M1"))
        str = str & cel.Value
    Next cel
    Debug.Print str
End Sub

Upvotes: 4

SJR
SJR

Reputation: 23081

If you just want the sheet name added to the formula, but I don't understand why.

With ActiveCell
    .FormulaR1C1 = "=CONCAT(" & .Parent.Name & "!RC[-13]:RC[-1])"
End With

Upvotes: 1

Related Questions