concatenate using set "search column for text" range & corresponding row - VBA Excel

[minddumps]

Hello All,

I am receiving incorrect data with this piece of my VBA.

            Set rngRankHeader = Range("A1:ZZ1").Find(What:="Rank", LookAt:=xlPart, _
                    MatchCase:=False, SearchFormat:=False) 'Find Rank Header 'LookIn:=xlValues,
                        
                        If rngRankHeader Is Nothing Then
                        MsgBox "A column with the header 'Rank' was not found; recheck your column names to ensure the 'Rank' column does have the header 'Rank'.", vbExclamation
                        Exit Sub
                        End If
                        
            LR = Cells(Rows.Count, rngNameHeader.Offset(, -4).Column).End(xlUp).Row 'I need to concatenate existing Column Header Row "Rank", Column Header "First Name", "MI", "Last Name";
            rngNameHeader.Offset(1, 3).Resize(LR - 1).FormulaR1C1 = _
            "=CONCATENATE(" & Cells(2, rngRankHeader.Column).Address(0, 0) & ","" "",RC[-2],"" "",RC[-1],"" "",RC[-3])" '********************************This will follow code from above; Select same column with formuals to paste values section"
                
            rngNameHeader.Offset(, 3).EntireColumn.Select
            Selection.Replace What:="'", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

The piece that's incorrect is

"& Cells(2,  rngRankHeader.Column).Address(0, 0) &

because it is returning a particular cell's data in the selected column and repeating it throughout every row vs giving me the column's cell's data within the corresponding row. I also tried using

& rngRankHeader.Address(1, 1, xlR2C1) &

and that didn't work either.

For example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]abc[/TD]
[TD]def[/TD]
[TD]=abcdef (as it should)
[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]ghi[/TD]
[TD]=abcdef (pulling from 1st cell instead of ghi)
[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]jkl[/TD]
[TD]=abcdef (pulling from 1st cell instead of jkl)
[/TD]
[/TR]
</tbody>[/TABLE]


Is anyone able to correct this for me?