Incorporating a variable in a CONCATANATE string in a macro

[wardie]

I am trying to incorporate a variable in a CONCATENATE string in a macro. I have a loop that I want to merge 3 values using CONCATENATE with Text1 and Text2 being fixed sources eg A1 & B1 but I need to increment the 3rd value each loop. My variable "ci" is incrementing but I cannot work out how to modify the line below that is bolded to give me the incremented value.

I admit I'm a huge amateur with Macros and this may be a simple solution but my googleFU is lacking in this one.

Please note that the code is not complete in this example. That is my task for tonight.

Sub Compile_Data()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim ri As Integer
    Dim ci As Integer
        
    StartRow = Range("StartRow")
    EndRow = Range("EndRow")
    
    If StartRow > EndRow Then
        Msg = "ERROR" & vbCrLf & "The Starting Row must be less than the ending row!"
        MsgBox Msg, vbCritical, APPNAME
    End If
    
    Sheets("Output").Select
    Range("A2").Select
    
    For ri = StartRow To EndRow
        Range("RowIndex") = ri
        ActiveCell.FormulaR1C1 = "=CONCATENATE(Field1,Field2,Field3)"
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        For ci = StartRow To EndRow
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = "=CONCATENATE(Field1, Field2, XXXXX)" <---Need the XXXXX field to be a cell reference that is incremented each loop
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Next ci
        Cells(ActiveCell.Row + 1, 1).Select
     Next ri
    
End Sub

Thanks for any help

 

[Scott Huish]

It looks like ri is supposed to be the row number, but what column for the cell reference?