Concat in Excel using VBA and output it to a string

Question

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.

Where do you have the sheet name, in the code, in another cell? — Scott Craner
– Scott Craner, Commented Sep 21, 2017 at 14:18
What do you mean bu sheets are dynamic? Do you create new sheets within the same spreadsheet or will you just change the name of the sheet every time? — ian0411
– ian0411, Commented Sep 21, 2017 at 14:19
I can actually take care of the sheet name. if you give the VBA Code that has the sheet mentioned in it as Sheet1 or something. Its just that, I want to output it to different strings in vba just by changing the sheet name. — Student of the Digital World
– Student of the Digital World, Commented Sep 21, 2017 at 14:20

Mrig · Accepted Answer · 2017-09-21 14:23:30Z

4

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

edited Sep 21, 2017 at 14:23

answered Sep 21, 2017 at 14:22

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Student of the Digital World Over a year ago

Yes, Just what I wanted actually. Thanks :)

SJR · Accepted Answer · 2017-09-21 14:20:50Z

1

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

answered Sep 21, 2017 at 14:20

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Collectives™ on Stack Overflow

Concat in Excel using VBA and output it to a string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related