For Next Loop Trouble

[bubbapost]

Hello,

The code below doesn't seem to advance to the next cell. Can anynone help me?

Sub NewConcatenate()
Dim i As Long
Dim FinalRow As Long
Dim NextRow As Long
Dim rCell As Range
FinalRow = Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row
Set rCell = ActiveCell
NextRow = rCell.Row
For i = 2 To FinalRow
    If rCell.Value <> "" Then
        rCell.Offset(0, 1).FormulaR1C1 = "=CONCATENATE(RC[-2],"", "",RC[-1])"
        NextRow = NextRow + 1
    End If
Next i
End Sub

Thank you!

 

[mikerickson]

The value of rCell is not changing inside the loop.

Try

Dim oneCell as Range

With ActiveCell.EntireColumn
    For Each oneCell in Range(.Cells(2,1), .Cells(.Rows.Count,1).End(xlup))
        With oneCell
            If .Value <> vbNullString Then
                .Offset(0,1).FormulaR1C1 = "=CONCATENATE(RC[-2],"""", """",RC[-1])"
            End If
        End With
    Next oneCell
End With

A non-looping approach might be used, but it doesn't check for the blank cells

With ActiveCell.EntireColumn
    With Range(.Cells(2,1), .Cells(.Rows.Count,1).End(xlup))
        .Offset(0,1).FormulaR1C1 = "=CONCATENATE(RC[-2],"""", """",RC[-1])"
    End With
End With

Also to get a quote mark inside a text literal, you need to double the quote (e.g. MsgBox " ""cat"" " will display "cat" )

 

[bubbapost]

SOLVED: For Next Loop Trouble

Thank you, that works awesome!

 

[Peter_SSs]

Re: SOLVED: For Next Loop Trouble

I think Mike's non-looping approach should work if the formula part was changed to

"=IF(RC[-1]="""","""",CONCATENATE(RC[-2],"""", """",RC[-1]))"