Horizontal Loop

Question

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

1. Loop through each row in the 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 columns 2 to 100 with a "|" in-between.

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

Answer 1

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

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

I hope this helps you.