CODEWITHSUNDEEP
vbaexcel
alphaService
alphaService

Reputation: 131

Concatenate Cell Values Dynamically And Copy Result to Another Worksheet

I have a worksheet ("Data") with x-columns and y-rows. I want to concatenate the values of a row and the concatenated result should be copied to a second worksheet ("Insert") in the first column of the same row.

I tried this VBA and get an error message

Sub InsertStatementRow()
Dim x As String, rng As Range, rng1 As Range, cel As Range
Dim ColMax As Integer
Dim i As Long

    Sheets("Data").Select
    Range("A1").Select

        ColMax = Cells(1, Columns.Count).End(xlToLeft).Column

        With Worksheets("Data")
        i = 1
        Set rng = Range(Cells(i, 1), Cells(i, ColMax))
        End With

        For Each cel In rng

            x = x & cel.Value

        Next

        Range(Sheets("Insert").Cells(i, 1)).Value = x

End Sub

Please show me what I am doing wrong by correcting my code. Thanks!

Upvotes: 0

Views: 1779

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

Use some "." :

Set rng = Range(.Cells(i, 1), .Cells(i, ColMax))

Upvotes: 1

Related Questions