I'm trying to write a macro that does the following:

1. Loop through each row in table1 starting with 1 to 50
2. Loop through each column of the respective row starting with 2 to 100
3. Concatenate for each row in column 1 all the single fields in column 2 to 100 with a "|" inbetween.

See my code below. I get an error for .Range.Cells(lRow, 1) = .Range.Cells(lRow, 1) & "|" & .Range.Cells(lRow, lCol)

Option Explicit

Sub horizontal_loop()

Dim lRow, lCol As Long

With Worksheets("table1")

    For lRow = 1 To 50
    
       For lCol = 2 To 100
    
            .Range.Cells(lRow, 1) = .Range.Cells(lRow, 1) & "|" & .Range.Cells(lRow, lCol)

       Next lCol
    
    Next lRow
    
End With
    
End Sub

CodePudding user response：

There is no need to use Range if you are using Cells.

.Cells(lRow, 1) = .Cells(lRow, 1) & "|" & .Cells(lRow, lCol)

CodePudding user response：

Alternatively with single loop

Sub horizontal_loop()

    Const STARTROW = 1
    Const ENDROW = 50
    Const STARTCOL = 2
    Const ENDCOL = 100
    
    Dim lRow As Long, ar, wsf As WorksheetFunction
    Set wsf = WorksheetFunction
    
    With Worksheets("table1")
        For lRow = STARTROW To ENDROW
            ar = .Cells(lRow, STARTCOL).Resize(, ENDCOL - STARTCOL   1)
            ar = wsf.Transpose(wsf.Transpose(ar))
            .Cells(lRow, 1) = Join(ar, "|")
        Next lRow
    End With
    
End Sub